﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using HZ.Supervise.Model;
using HZ.Supervise.Model.View;
using HZ.Data;
using HZ.Common;

namespace HZ.Supervise.BLL
{
    public class SuperviseContext : HZ.Supervise.Model.ExecuteContext, HZ.Supervise.IBLL.IExecuteContext
    {
        /// <summary>
        /// 批量删除消息
        /// </summary>
        /// <param name="message"></param>
        /// <returns></returns>
        public int DeleteMessage_Batch(IList<Model.Message> message)
        { 
            foreach (var item in message)
            { 
                ThrowHelper.Throw(item.MessageID != null, "message.MessageID != null");
            }
            return DAL.Supervise.DeleteMessage_Batch(message);
        }
        /// <summary>
        /// 修改消息(修改后消息会变成未读状态，发送时间也重新刷新)
        /// </summary> 
        public int UpdateMessage(Model.Message message)
        { 
            ThrowHelper.Throw(message.Status != null);
            return DAL.Supervise.UpdateMessage(message); 
        }
        /// <summary>
        /// 批量发送消息
        /// (消息粒度到机构到机构只有主用户或主用户授权用户才能够发送消息)
        /// </summary> 
        public int AddMessage_Batch(IList<Model.Message> message)
        { 
            foreach (var item in message)
            {
                ThrowHelper.Throw(item.MessageID != null);
                ThrowHelper.Throw(item.Reciever != null);
                //item.Sender = BLL_UserSession.GetCurrentUser().Org.OrgID;
                item.SendTime = DateTime.Now;
            }
            return DAL.Supervise.AddMessage_Batch(message);

        } 
        /// <summary>
        /// 获取已经发送的消息
        /// (机构ID无需传入)
        /// </summary> 
        public IList<Model.View.Message> GetMessageSent(Model.Query.QMessage where, int currentpage, int pagesize, out int totalrecord)
        {
            return DAL.Supervise.GetMessageSent(where, currentpage, pagesize, out totalrecord);
        }
        /// <summary>
        /// 获取消息[已收信息]
        /// (机构ID无需传入)
        /// </summary> 
        public IList<Model.View.Message> GetMessage(Model.Query.QMessage where, int currentpage, int pagesize, out int totalrecord)
        {
            return DAL.Supervise.GetMessage(where, currentpage, pagesize, out totalrecord);
        }
        /// <summary>
        /// 获取已接收的公告
        /// </summary> 
        public IList<Model.View.Notice> GetRecievedNotice(Model.Query.QNotice where, int currentpage, int pagesize, out int totalrecord)
        { 
            where.AuditStatus = true;
            //where.Reciever = BLL_UserSession.GetCurrentUser().UserRole.RoleID;
            return DAL.DAL_Helper.GetPagedView<Model.View.Notice, Model.Query.QNotice>(where, currentpage, pagesize, out totalrecord);
        }
        /// <summary>
        /// 获取已发布的公告
        /// </summary> 
        public IList<Model.View.Notice> GetNotice(Model.Query.QNotice where, int currentpage, int pagesize, out int totalrecord)
        { 
            where.AuditStatus = true;
            //where.Sender = BLL_UserSession.GetCurrentUser().Org.OrgID;
            return DAL.Supervise.GetAuditNotice(where, currentpage, pagesize, out totalrecord);
        }
        /// <summary>
        /// 获取待审核的公告
        /// </summary> 
        public IList<Model.View.Notice> GetAuditNotice(Model.Query.QNotice where, int currentpage, int pagesize, out int totalrecord)
        { 
            where.AuditStatus = false;
            //where.Sender = BLL_UserSession.GetCurrentUser().Org.OrgID; 
            return DAL.Supervise.GetAuditNotice(where, currentpage, pagesize, out totalrecord);
        }
        /// <summary>
        /// 日志管理---日志查看
        /// </summary> 
        public DataTable GetLog(string str, int currentPage, int pageSize, out int totalRecord)
        {
            return HZ.Supervise.DAL.Supervise.GetLog(str, currentPage, pageSize, out totalRecord);
        }
        /// <summary>
        /// 获取有效地区产品列表
        /// 2010.3.29 by 张雄 create
        /// </summary>
        /// <param name="proce"></param>
        /// <param name="currentpage"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalrecord"></param>
        /// <returns>区域产品列表</returns>
        public DataTable GetCompanyGoodsListArea(HZ.Supervise.Model.View.AreaProcureCatalog proce, int currentpage, int pagesize, string ConStrAreaId, out int totalrecord)
        {

            StringBuilder sb = new StringBuilder();

            if (!string.IsNullOrEmpty(proce.Areaid) && proce.Areaid != ConStrAreaId)
            {
                if (proce.Areaid.Substring(4, 2) == "00")
                {
                    sb.AppendFormat(" and areaid like '{0}%'", proce.Areaid.Substring(0, 4));
                }
                else
                {
                    sb.AppendFormat(" and areaid ='{0}'", proce.Areaid);
                }
            }
            //if (!string.IsNullOrEmpty(proce.SortSource.ToString()))
            //{
            //    sb.AppendFormat(" and SortSource = '{0}'", proce.SortSource);
            //}
            if (!string.IsNullOrEmpty(proce.Unit))
            {
                sb.AppendFormat(" and Unit = '{0}'", proce.Unit);
            }
            if (!string.IsNullOrEmpty(proce.ProductName))
            {
                sb.AppendFormat(" and ProductName like '%{0}%'", proce.ProductName);
            }
            if (!string.IsNullOrEmpty(proce.MedicineModelName))
            {
                sb.AppendFormat(" and MedicineModelName like '%{0}%'", proce.MedicineModelName);
            }
            if (!string.IsNullOrEmpty(proce.Outlookc))
            {
                sb.AppendFormat(" and Outlookc like '%{0}%'", proce.Outlookc);
            }
            if (!string.IsNullOrEmpty(proce.CompanyName_SC))
            {
                sb.AppendFormat(" and CompanyName_SC like '%{0}%'", proce.CompanyName_SC);
            }
            if (!string.IsNullOrEmpty(proce.Pack))
            {
                sb.AppendFormat(" and Pack like '%{0}%'", proce.Pack);
            }
            string sql = @"select * from  View_AreaProcureCatalog  where 1=1 and ISUSING=1" + sb.ToString() + " order by ProductName,MedicineModelName,Outlookc desc";
            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
            des.CurrentPage = currentpage;
            totalrecord = des.TotalCount;
            return des.GetSpecailPage();
        }

        /// <summary>
        /// 执行上下文
        /// </summary>
        /// <returns></returns>
        public override DataTable Execute()
        {
            if (QueryObject == null)
            {
                throw new ArgumentNullException("QueryObject 必须赋值后才能执行");
            }
            return HZ.Data.DbHelperFactory.Create().ExecuteTable(HZ.Supervise.DAL.BuildSql.GetSql<Model.SuperviseQuery>(QueryObject));
            // return null;
        }

        /// <summary>
        /// 各县区配送率汇总
        /// </summary>
        /// <param name="hosproce"></param>
        /// <param name="currentpage"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalrecord"></param>
        /// <returns></returns>
        public DataTable getAreaXianquList(string areaid, string orderby, string source, int currentpage, int pagesize, out int totalrecord)
        {
            return HZ.Supervise.DAL.Supervise.getAreaXianquList(areaid, orderby, source, currentpage, pagesize, out totalrecord);
        }

        public DataTable ExecutePage(int currentpage, int pagesize, out int totalrecord)
        {
            if (QueryObject == null)
            {
                throw new ArgumentNullException("QueryObject 必须赋值后才能执行");
            }

            string sql = HZ.Supervise.DAL.BuildSql.GetSql<Model.SuperviseQuery>(QueryObject);

            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
            des.CurrentPage = currentpage;
            totalrecord = des.TotalCount;
            return des.GetSpecailPage();

        }

        /// <summary>
        /// 医院
        /// 所有采购类型
        /// by张雄  2011.5.12
        /// </summary>
        /// <param name="currentpage">页数</param>
        /// <param name="pagesize">条数</param>
        /// <param name="totalrecord"></param>
        /// <returns>所有药品信息</returns>
        public DataTable ExecutePageNew(string where, string order, int currentpage, int pagesize, out int totalrecord)
        {
            string sql = string.Format(@"
                                        SELECT ProductName,MedicineModelName,Outlookc,Factor,Unit,CompanyName_SC,SortName,CompanyName_TB,
                                        GoodsName,Pack,QualityLevel,ProcurePrice,B.SortSource,B.ProcureCatalogID,PurchaseCountTotal, 
                                        PurchaseAmountTotal, WareHouseCountTotal,DistributeRate, WareHouseRate, WareHouseAmountTotal ,
                                        DistributeAmountTotal FROM View_ProcureCatalog_TB tab INNER JOIN (
                                        SELECT ProcureCatalogID,SortSource,
                                        SUM(isnull(DistributeAmount,0)) AS DistributeAmountTotal,
                                        SUM(isnull(PurchaseAmount,0)) AS PurchaseAmountTotal,
                                        SUM(isnull(PurchaseCount,0)) AS PurchaseCountTotal,
                                        SUM(isnull(WarehouseCount,0)) AS WareHouseCountTotal,
                                        case  when SUM(isnull(WarehouseAmount,0)) =0 then 0 
                                        when SUM(isnull(PurchaseAmount,0)) = 0 then 0 
                                        when convert(decimal(18,3),SUM(isnull(WarehouseAmount,0)) / SUM(isnull(PurchaseAmount,0))) >1 then 1 
                                        else convert(decimal(18,3),SUM(isnull(WarehouseAmount,0)) / SUM(isnull(PurchaseAmount,0))) end WarehouseRate, 
                                        case when SUM(isnull(DistributeAmount,0)) = 0 then 0 
                                        when SUM(isnull(PurchaseAmount,0)) = 0 then 0 
                                        when convert(decimal(18,3),SUM(isnull(DistributeAmount,0)) / SUM(isnull(PurchaseAmount,0))) > 1 then 1  
                                        else convert(decimal(18,3),SUM(isnull(DistributeAmount,0)) / SUM(isnull(PurchaseAmount,0))) end DistributeRate ,
                                        SUM(isnull(WarehouseAmount,0)) AS WareHouseAmountTotal FROM CACHE_STAT_Goods 
                                        WHERE 1=1 {0}  GROUP BY ProcureCatalogID,SortSource
                                        ) AS B ON tab.ProcureCatalogID = B.ProcureCatalogID ORDER BY {1} ", where, order);
            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
            des.CurrentPage = currentpage;
            totalrecord = des.TotalCount;
            return des.GetSpecailPage();
        }

        /// <summary>
        /// 配送企业
        /// 所有采购类型
        /// by张雄  2011.5.12
        /// </summary>
        /// <param name="where">sql where条件</param>
        /// <param name="currentpage">当前页</param>
        /// <param name="pagesize">每页的行数</param>
        /// <param name="totalrecord">参数</param>
        /// <returns>所有采购类型</returns>
        public DataTable ExecutePageCom(string where, string order, int currentpage, int pagesize, out int totalrecord)
        {
            //判断如果配送率，入库率大于1 by张雄
            string sql = string.Format(@"SELECT B.SortSource, ProductName,MedicineModelName,Outlookc,Factor,Unit,CompanyName_SC,SortName,CompanyName_TB,GoodsName,
                                        Pack,QualityLevel,ProcurePrice,B.ProcureCatalogID,PurchaseCountTotal, PurchaseAmountTotal, DistributeRate, 
                                        DistributeCountTotal, DistributeAmountTotal,WarehouseAmountTotal,WarehouseRate FROM View_ProcureCatalog_TB tab 
                                        INNER JOIN (
                                        SELECT MAX( SortSource) SortSource, ProcureCatalogID,  
                                        case when ISNULL(SUM(PurchaseAmount),0) = 0 then 0 
                                        when CONVERT(FLOAT,SUM(DistributeAmount)) / SUM(PurchaseAmount) > 1 then 1  
                                        ELSE CONVERT(FLOAT,SUM(DistributeAmount)) / SUM(PurchaseAmount) END AS DistributeRate,
                                        case when isnull(SUM(PurchaseAmount),0) = 0then 0 
                                        when CONVERT(decimal(18,3),SUM(WarehouseAmount) / SUM(PurchaseAmount)) > 1 then 1 
                                        else CONVERT(decimal(18,3),SUM(WarehouseAmount) / SUM(PurchaseAmount)) end as WarehouseRate,
                                        SUM(PurchaseAmount) AS PurchaseAmountTotal,SUM(PurchaseCount) AS PurchaseCountTotal ,
                                        SUM(DistributeCount) AS DistributeCountTotal,SUM(WarehouseAmount) AS WarehouseAmountTotal,
                                        sum(DistributeAmount) as DistributeAmountTotal FROM CACHE_STAT_Goods WHERE 1=1 {0} 
                                        GROUP BY ProcureCatalogID) AS B ON tab.ProcureCatalogID = B.ProcureCatalogID ORDER BY {1}", where, order);
            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
            des.CurrentPage = currentpage;
            totalrecord = des.TotalCount;
            return des.GetSpecailPage();
        }
        /// <summary>
        /// 生产企业
        /// 所有采购类型
        /// by张雄  2011.5.12
        /// </summary>
        /// <param name="where">sql where条件</param>
        /// <param name="currentpage">当前页</param>
        /// <param name="pagesize">每页的行数</param>
        /// <param name="totalrecord">参数</param>
        /// <returns>所有采购类型</returns>
        public DataTable ExecutePage_SC(string where, string order, int currentpage, int pagesize, out int totalrecord)
        {
            string sql = string.Format("SELECT  B.SortSource,ProductName,MedicineModelName,Outlookc,Factor,Unit,CompanyName_SC,SortName,CompanyName_TB,GoodsName,Pack,QualityLevel,ProcurePrice,B.ProcureCatalogID,PurchaseCountTotal, PurchaseAmountTotal, DistributeRate, DistributeCountTotal, DistributeAmountTotal,WarehouseAmountTotal,WarehouseRate FROM  View_ProcureCatalog_TB tab INNER JOIN (SELECT SortSource,ProcureCatalogID, case when isnull(SUM(PurchaseAmount),0) = 0 then 0 when CONVERT(FLOAT,SUM(DistributeAmount)) / SUM(PurchaseAmount) > 1 then 1 ELSE CONVERT(FLOAT,SUM(DistributeAmount)) / SUM(PurchaseAmount) END AS DistributeRate, case when isnull(SUM(PurchaseAmount),0) = 0 then 0 when CONVERT(decimal(18,3),SUM(WarehouseAmount) / SUM(PurchaseAmount)) > 1 then 1 else CONVERT(decimal(18,3),SUM(WarehouseAmount) / SUM(PurchaseAmount)) end as WarehouseRate,SUM(PurchaseAmount) AS PurchaseAmountTotal,SUM(PurchaseCount) AS PurchaseCountTotal ,SUM(DistributeCount) AS DistributeCountTotal,SUM(WarehouseAmount) AS WarehouseAmountTotal,sum(DistributeAmount) as DistributeAmountTotal FROM CACHE_STAT_Goods WHERE 1=1 {0} GROUP BY ProcureCatalogID,SortSource) AS B ON tab.ProcureCatalogID = B.ProcureCatalogID ORDER BY {1}", where, order);
            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
            des.CurrentPage = currentpage;
            totalrecord = des.TotalCount;
            return des.GetSpecailPage();
        }

        /// <summary>
        /// 曹鹏 
        /// 2011年5月12日9:15:28
        /// 地区月报
        /// </summary>
        /// <param name="where"></param>
        /// <param name="currentpage"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalrecord"></param>
        public DataTable StatisticsAreaByMonth(string where)
        {

            string sql = string.Format("select SUM(PurchaseAmount) as PurchaseAmountTotal,SUM(DistributeAmount) as DistributeAmountTotal,case when SUM(DistributeAmount) =0 then 0 when SUM(PurchaseAmount)=0 then 0 else CONVERT(decimal(18,3),CONVERT(float, SUM(DistributeAmount)) / SUM(PurchaseAmount)) end AS DistributeRate,case when SUM(WarehouseAmount) =0 then 0 when SUM(PurchaseAmount)=0 then 0 else CONVERT(decimal(18,3),CONVERT(float, SUM(WarehouseAmount)) / SUM(PurchaseAmount)) end AS WareHouseRate,sum(WarehouseAmount)as WarehouseAmountTotal,SortSource,daytime  from CACHE_STAT_Goods WHERE 1=1 {0} group by SortSource,daytime", where);

            return HZ.Data.DbHelperFactory.Create().ExecuteTable(sql);

        }

        /// <summary>
        /// 曹鹏 马良修改于0519
        /// 2011年5月12日10:40:32
        /// 地区季报
        /// </summary>
        /// <param name="where"></param>
        /// <returns></returns>
        public DataTable StatisticsAreaBySeason(string where)
        {

            string sql = string.Format("select SUM(PurchaseAmount) as PurchaseAmountTotal,SUM(DistributeAmount) as DistributeAmountTotal,case when SUM(DistributeAmount) =0 then 0 when SUM(PurchaseAmount)=0 then 0 when CONVERT(decimal(18,3),CONVERT(float, SUM(DistributeAmount)) / SUM(PurchaseAmount))>1 then 1 else CONVERT(decimal(18,3),CONVERT(float, SUM(DistributeAmount)) / SUM(PurchaseAmount)) end AS DistributeRate,case when SUM(WarehouseAmount) =0 then 0 when SUM(PurchaseAmount)=0 then 0 when CONVERT(decimal(18,3),CONVERT(float, SUM(WarehouseAmount)) / SUM(PurchaseAmount))>1 then 1 else CONVERT(decimal(18,3),CONVERT(float, SUM(WarehouseAmount)) / SUM(PurchaseAmount)) end AS WareHouseRate,sum(WarehouseAmount)as WarehouseAmountTotal,daytime  from CACHE_STAT_Goods WHERE 1=1 {0} group by daytime", where);

            return HZ.Data.DbHelperFactory.Create().ExecuteTable(sql);

        }

        /// <summary>
        /// 马良 地区季报饼图数据
        /// </summary>
        /// <param name="where"></param>
        /// <returns></returns>
        public DataTable StatisticsAreaBySeasonPie(string where)
        {

            string sql = string.Format(@"select SUM(PurchaseAmount) as PurchaseAmountTotal,SUM(DistributeAmount) as DistributeAmountTotal,case when SUM(DistributeAmount) =0 then 0 
                    when SUM(PurchaseAmount)=0 then 0 when CONVERT(decimal(18,3),CONVERT(float, SUM(DistributeAmount)) / SUM(PurchaseAmount))>1 then 1 
                    else CONVERT(decimal(18,3),CONVERT(float, SUM(DistributeAmount)) / SUM(PurchaseAmount)) end AS DistributeRate,case when SUM(WarehouseAmount) =0 then 0 
                    when SUM(PurchaseAmount)=0 then 0 when CONVERT(decimal(18,3),CONVERT(float, SUM(WarehouseAmount)) / SUM(PurchaseAmount))>1 then 1
                    else CONVERT(decimal(18,3),CONVERT(float, SUM(WarehouseAmount)) / SUM(PurchaseAmount)) end AS WareHouseRate,sum(WarehouseAmount)as WarehouseAmountTotal,
                    case SortSource when '1' then '第一批基本药物' else '全部'  end as SortSource from CACHE_STAT_Goods WHERE 1=1 {0} group by SortSource", where);

            return HZ.Data.DbHelperFactory.Create().ExecuteTable(sql);

        }

        /// <summary>
        /// 马良 0519
        /// 地区年报
        /// </summary>
        /// <param name="where"></param>
        /// <returns></returns>
        public DataTable StatisticsAreaByYear(string where)
        {
            //判断是否为空
            string sql = string.Format("select SUM(isnull(PurchaseAmount,0)) as PurchaseAmountTotal,SUM(isnull(DistributeAmount,0)) as DistributeAmountTotal,case when SUM(ISNULL(DistributeAmount,0)) =0 then 0 when SUM(ISNULL(PurchaseAmount,0))=0 then 0 when CONVERT(decimal(18,3),CONVERT(float, SUM(ISNULL(DistributeAmount,0))) / SUM(ISNULL(PurchaseAmount,0)))>1 then 1  else CONVERT(decimal(18,3),CONVERT(float, SUM(ISNULL(DistributeAmount,0))) / SUM(ISNULL(PurchaseAmount,0))) end AS DistributeRate,case when SUM(ISNULL(WarehouseAmount,0)) =0 then 0 when SUM(ISNULL(PurchaseAmount,0))=0 then 0 when CONVERT(decimal(18,3),CONVERT(float, SUM(ISNULL(WarehouseAmount,0))) / SUM(ISNULL(PurchaseAmount,0)))>1 then 1 else CONVERT(decimal(18,3),CONVERT(float, SUM(ISNULL(WarehouseAmount,0))) / SUM(ISNULL(PurchaseAmount,0))) end AS WareHouseRate,sum(ISNULL(WarehouseAmount,0))as WarehouseAmountTotal,daytime  from CACHE_STAT_Goods WHERE 1=1  {0}group by daytime ORDER BY DayTime", where);
            return HZ.Data.DbHelperFactory.Create().ExecuteTable(sql);

        }

        /// <summary>
        /// 马良 地区年报饼图数据
        /// </summary>
        /// <param name="where"></param>
        /// <returns></returns>
        public DataTable StatisticsAreaByYearPie(string where)
        {

            string sql = string.Format(@"select SUM(PurchaseAmount) as PurchaseAmountTotal,SUM(DistributeAmount) as DistributeAmountTotal,case when SUM(DistributeAmount) =0 then 0 
                    when SUM(PurchaseAmount)=0 then 0 when CONVERT(decimal(18,3),CONVERT(float, SUM(DistributeAmount)) / SUM(PurchaseAmount))>1 then 1 
                    else CONVERT(decimal(18,3),CONVERT(float, SUM(DistributeAmount)) / SUM(PurchaseAmount)) end AS DistributeRate,case when SUM(WarehouseAmount) =0 then 0 
                    when SUM(PurchaseAmount)=0 then 0 when CONVERT(decimal(18,3),CONVERT(float, SUM(WarehouseAmount)) / SUM(PurchaseAmount))>1 then 1
                    else CONVERT(decimal(18,3),CONVERT(float, SUM(WarehouseAmount)) / SUM(PurchaseAmount)) end AS WareHouseRate,sum(WarehouseAmount)as WarehouseAmountTotal,
                    case SortSource when '0' then '日常采购' when '1' then '临时采购 ' when '2' then '应急采购' else '无'  end as SortSource 
                    from CACHE_STAT_Goods WHERE 1=1 {0} group by SortSource", where);

            return HZ.Data.DbHelperFactory.Create().ExecuteTable(sql);

        }

        /// <summary>
        /// 企业年报
        /// by张雄  2011.5.20
        /// </summary>
        /// <param name="where">where条件 参数</param>
        /// <returns>企业年报数据</returns>
        public DataTable StatisticsComByYear(string where)
        {
            string sql = string.Format("select SUM(PurchaseAmount) as PurchaseAmountTotal,SUM(DistributeAmount) as DistributeAmountTotal,case when SUM(DistributeAmount) =0 then 0 when SUM(PurchaseAmount)=0 then 0 when CONVERT(decimal(18,3),CONVERT(float, SUM(DistributeAmount)) / SUM(PurchaseAmount))>1 then 1  else CONVERT(decimal(18,3),CONVERT(float, SUM(DistributeAmount)) / SUM(PurchaseAmount)) end AS DistributeRate,case when SUM(WarehouseAmount) =0 then 0 when SUM(PurchaseAmount)=0 then 0 when CONVERT(decimal(18,3),CONVERT(float, SUM(WarehouseAmount)) / SUM(PurchaseAmount))>1 then 1 else CONVERT(decimal(18,3),CONVERT(float, SUM(WarehouseAmount)) / SUM(PurchaseAmount)) end AS WareHouseRate,sum(WarehouseAmount)as WarehouseAmountTotal,daytime  from CACHE_STAT_Goods WHERE 1=1 {0} group by daytime ORDER BY DayTime", where);
            return HZ.Data.DbHelperFactory.Create().ExecuteTable(sql);
        }

        /// <summary>
        /// 企业年报    饼图数据
        /// by张雄  2011.5.20
        /// </summary>
        /// <param name="where">where条件 参数</param>
        /// <returns>企业年报数据</returns>
        public DataTable StatisticsComByYearPie(string where)
        {
            string sql = string.Format(@"select SUM(PurchaseAmount) as PurchaseAmountTotal,SUM(DistributeAmount) as DistributeAmountTotal,case when SUM(DistributeAmount) =0 then 0 
                    when SUM(PurchaseAmount)=0 then 0 when CONVERT(decimal(18,3),CONVERT(float, SUM(DistributeAmount)) / SUM(PurchaseAmount))>1 then 1 
                    else CONVERT(decimal(18,3),CONVERT(float, SUM(DistributeAmount)) / SUM(PurchaseAmount)) end AS DistributeRate,case when SUM(WarehouseAmount) =0 then 0 
                    when SUM(PurchaseAmount)=0 then 0 when CONVERT(decimal(18,3),CONVERT(float, SUM(WarehouseAmount)) / SUM(PurchaseAmount))>1 then 1
                    else CONVERT(decimal(18,3),CONVERT(float, SUM(WarehouseAmount)) / SUM(PurchaseAmount)) end AS WareHouseRate,sum(WarehouseAmount)as WarehouseAmountTotal,
                    case SortSource when '0' then '日常采购' when '1' then '临时采购' when '2' then '应急采购' else '全部' end as SortSource from CACHE_STAT_Goods WHERE 1=1 {0} group by SortSource", where);

            return HZ.Data.DbHelperFactory.Create().ExecuteTable(sql);
        }

        /// <summary>
        /// 企业季报表
        /// by张雄  2011.5.20
        /// </summary>
        /// <param name="where">where条件 参数</param>
        /// <returns>企业季报表数据</returns>
        public DataTable StatisticsComBySeason(string where)
        {
            string sql = string.Format("select SUM(PurchaseAmount) as PurchaseAmountTotal,SUM(DistributeAmount) as DistributeAmountTotal,case when SUM(DistributeAmount) =0 then 0 when SUM(PurchaseAmount)=0 then 0 when CONVERT(decimal(18,3),CONVERT(float, SUM(DistributeAmount)) / SUM(PurchaseAmount))>1 then 1  else CONVERT(decimal(18,3),CONVERT(float, SUM(DistributeAmount)) / SUM(PurchaseAmount)) end AS DistributeRate,case when SUM(WarehouseAmount) =0 then 0 when SUM(PurchaseAmount)=0 then 0 when CONVERT(decimal(18,3),CONVERT(float, SUM(WarehouseAmount)) / SUM(PurchaseAmount))>1 then 1 else CONVERT(decimal(18,3),CONVERT(float, SUM(WarehouseAmount)) / SUM(PurchaseAmount)) end AS WareHouseRate,sum(WarehouseAmount)as WarehouseAmountTotal,daytime  from CACHE_STAT_Goods WHERE 1=1 {0} group by daytime ORDER BY DayTime", where);
            return HZ.Data.DbHelperFactory.Create().ExecuteTable(sql);
        }

        /// <summary>
        /// 企业季报表
        /// by张雄  2011.5.20
        /// </summary>
        /// <param name="where">where条件 参数</param>
        /// <returns>企业季报表数据</returns>
        public DataTable StatisticsComBySeasonPie(string where)
        {
            string sql = string.Format(@"select SUM(PurchaseAmount) as PurchaseAmountTotal,SUM(DistributeAmount) as DistributeAmountTotal,case when SUM(DistributeAmount) =0 then 0 
                    when SUM(PurchaseAmount)=0 then 0 when CONVERT(decimal(18,3),CONVERT(float, SUM(DistributeAmount)) / SUM(PurchaseAmount))>1 then 1 
                    else CONVERT(decimal(18,3),CONVERT(float, SUM(DistributeAmount)) / SUM(PurchaseAmount)) end AS DistributeRate,case when SUM(WarehouseAmount) =0 then 0 
                    when SUM(PurchaseAmount)=0 then 0 when CONVERT(decimal(18,3),CONVERT(float, SUM(WarehouseAmount)) / SUM(PurchaseAmount))>1 then 1
                    else CONVERT(decimal(18,3),CONVERT(float, SUM(WarehouseAmount)) / SUM(PurchaseAmount)) end AS WareHouseRate,sum(WarehouseAmount)as WarehouseAmountTotal,
                   case SortSource when '0' then '日常采购' when '1' then '临时采购' when '2' then '应急采购' else '无'  end as SortSource from CACHE_STAT_Goods WHERE 1=1 {0} group by SortSource", where);

            return HZ.Data.DbHelperFactory.Create().ExecuteTable(sql);
        }


        /// <summary>
        /// 曹鹏
        /// 2011年5月6日9:14:29
        /// 药品对比
        /// </summary>
        /// <param name="where"></param>
        /// <returns></returns>
        public DataTable ExecutePro(string where)
        {

            string sql = string.Format("select ProductName, SortSource,SUM(PurchaseAmount) as PurchaseAmountTotal,SUM(DistributeAmount) as DistributeAmountTotal, SUM(WarehouseAmount) as WarehouseAmountTotal,case when SUM(DistributeAmount)=0 then 0 when SUM(PurchaseAmount)=0 then 0 when CONVERT(decimal(18,3),CONVERT(float, SUM(DistributeAmount)) / CONVERT(float, SUM(PurchaseAmount)))>1 then 1 else CONVERT(decimal(18,3),CONVERT(float, SUM(DistributeAmount)) / CONVERT(float, SUM(PurchaseAmount))) end as DistributeRate,case when SUM(WarehouseAmount)=0 then 0 when SUM(PurchaseAmount)=0 then 0 when CONVERT(decimal(18,3),CONVERT(float,sum(WarehouseAmount))/CONVERT(float,Sum(PurchaseAmount)))>1 then 1 else  CONVERT(decimal(18,3),CONVERT(float,sum(WarehouseAmount))/CONVERT(float,Sum(PurchaseAmount))) end as WareHouseRate  from CACHE_STAT_Goods where 1=1 {0} group by SortSource,ProductName order by ProductName,sortsource asc ", where);

            return HZ.Data.DbHelperFactory.Create().ExecuteTable(sql);

        }
        /// <summary>
        /// 曹鹏
        /// 2011年5月5日21:02:42
        /// 比对医院
        /// </summary>
        /// <param name="strHospitalId">查询条件</param>
        /// <returns></returns>
        public DataTable ExecuteHospital(string where)
        {

            string sql = string.Format("select HospitalName,HospitalID,SortSorce as SortSource,SUM(PurchaseAmount) as PurchaseAmountTotal,SUM(DistributeAmount) as DistributeAmountTotal,SUM(WarehouseAmount) as WarehouseAmountTotal,case when SUM(DistributeAmount)=0 then 0 when SUM(PurchaseAmount)=0 then 0 when CONVERT(decimal(18,3),CONVERT(float, SUM(DistributeAmount)) / CONVERT(float, SUM(PurchaseAmount)))>1 then 1 else CONVERT(decimal(18,3),CONVERT(float, SUM(DistributeAmount)) / CONVERT(float, SUM(PurchaseAmount))) end as DistributeRate,case when SUM(WarehouseAmount)=0 then 0 when SUM(PurchaseAmount)=0 then 0 when CONVERT(decimal(18,3),CONVERT(float, SUM(WarehouseAmount)) / CONVERT(float, SUM(PurchaseAmount)))>1 then 1 else CONVERT(decimal(18,3),CONVERT(float, SUM(WarehouseAmount)) / CONVERT(float, SUM(PurchaseAmount))) end as WareHouseRate from dbo.CACHE_STAT where 1=1 {0} group by HospitalName,HospitalID,SortSorce  order by hospitalName asc", where);

            return HZ.Data.DbHelperFactory.Create().ExecuteTable(sql);

        }

        public DataTable ExecutePSData(int currentpage, int pagesize, out int totalrecord)
        {
            string str = "SELECT  CompanyName_PS,"
                                + "CompanyID_PS,"
                                + "Case  WHEN SUM(DistributeCount)=0 THEN 0 "
                                + "WHEN SUM(PurchaseCount)=0 THEN 0  "
                                + "ELSE  CONVERT(FLOAT,SUM(DistributeCount*Price)) / SUM(PurchaseAmount) END   AS DistributeRate,"
                                + "SUM(PurchaseAmount) AS PurchaseAmountTotal,"
                                + "Case when sum(WareHouseCount)=0 then 0 "
                                + "when sum(PurchaseCount)=0 then 0 "
                                + "else CONVERT(FLOAT,SUM(WareHouseAmount)) / SUM(PurchaseAmount) end  AS WareHouseRate,"
                                + "SUM(WarehouseAmount) AS WareHouseAmountTotal,"
                                + "SUM(DistributeCount*Price) AS DistributeAmountTotal,"
                                + "COUNT (DISTINCT ProcureCatalogID) AS DrugCount "
                        + "FROM View_Supervise "
                        + "WHERE 1=1  "
                        + "AND IsUsing = 1 "
                        + "and CompanyName_PS is not null "
                        + "GROUP BY CompanyName_PS,CompanyID_PS order by CompanyName_PS,CompanyID_PS ";

            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(str, pagesize);
            des.CurrentPage = currentpage;
            totalrecord = des.TotalCount;
            return des.GetSpecailPage();

        }

        /// <summary>
        /// 
        /// </summary>
        /// sql语句入库率，配送率算法改变 by：zhangxiong
        /// <param name="currentpage"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalrecord"></param>
        /// <param name="strSortSorce"></param>
        /// <param name="strOrderby"></param>
        /// <param name="strAreaId"></param>
        /// <param name="strDaytime"></param>
        /// <returns></returns>
        public DataTable ExecutePageFirst(int currentpage, int pagesize, out int totalrecord, string strSortSorce, string strOrderby, string strAreaId, string strDaytime)
        {
            string sql = "";
            if (strSortSorce == "-1")
            {

                if (strAreaId.Substring(4, 2) == "00")
                {

                    sql = string.Format(@"
                                            SELECT b.AreaName,SortSorce as SortSource,
                                            SUM(isnull(PurchaseAmount,0)) AS PurchaseAmountTotal,
                                            SUM(isnull(PurchaseCount,0)) AS PurchaseCountTotal,
                                            SUM(isnull(WarehouseAmount,0)) AS WareHouseAmountTotal,
                                            SUM(isnull(WarehouseCount,0)) AS WareHouseCountTotal,
                                            SUM(isnull(DistributeAmount,0)) AS DistributeAmountTotal,
                                            case when SUM(isnull(WarehouseAmount,0))=0 then 0 
                                            when SUM(isnull(PurchaseAmount,0))=0 then 0
                                            when  SUM(isnull(WarehouseAmount,0)) / SUM  (isnull(PurchaseAmount,0))>1 then 1 
                                            else  convert(decimal(18,3),SUM(isnull(WarehouseAmount,0)) / SUM(isnull(PurchaseAmount,0))) end WarehouseRate,
                                            case when SUM(isnull(DistributeAmount,0))=0 then 0 when SUM(isnull(PurchaseAmount,0))=0 then 0
                                            when  SUM(isnull(DistributeAmount,0)) / SUM  (isnull(PurchaseAmount,0))>1 then 1 
                                            else   convert(decimal(18,3),SUM(isnull(DistributeAmount,0)) / SUM(isnull(PurchaseAmount,0))) end DistributeRate 
                                            FROM CACHE_STAT a inner join TB_Area b on  SUBSTRING(a.AreaID,1,4)+'00' = b.AreaID  
                                            and SortSorce in (0,1,2) and b.AreaID like '{0}' and a.DayTime = '{1}' 
                                            Group By SortSorce,b.AreaName ORDER BY {2}", strAreaId.Substring(0, 4) + "%", strDaytime, strOrderby);
                }
                else
                {
                    sql = string.Format(@"
                                            SELECT b.AreaName,SortSorce as SortSource,
                                            SUM(isnull(PurchaseAmount,0)) AS PurchaseAmountTotal,
                                            SUM(isnull(PurchaseCount,0)) AS PurchaseCountTotal,
                                            SUM(isnull(WarehouseAmount,0)) AS WareHouseAmountTotal,
                                            SUM(isnull(WarehouseCount,0)) AS WareHouseCountTotal,
                                            SUM(isnull(DistributeAmount,0)) AS DistributeAmountTotal,
                                            case when SUM(isnull(WarehouseAmount,0))=0 then 0 
                                            when SUM(isnull(PurchaseAmount,0))=0 then 0
                                            when  SUM(isnull(WarehouseAmount,0)) / SUM  (isnull(PurchaseAmount,0))>1 then 1 
                                            else  convert(decimal(18,3),SUM(isnull(WarehouseAmount,0)) / SUM(isnull(PurchaseAmount,0))) end WarehouseRate,
                                            case when SUM(isnull(DistributeAmount,0))=0 then 0 
                                            when SUM(isnull(PurchaseAmount,0))=0 then 0
                                            when  SUM(isnull(DistributeAmount,0)) / SUM  (isnull(PurchaseAmount,0))>1 then 1 
                                            else  convert(decimal(18,3),SUM(isnull(DistributeAmount,0)) / SUM(isnull(PurchaseAmount,0))) end DistributeRate 
                                            FROM CACHE_STAT a inner join TB_Area b on  a.AreaID = b.AreaID  
                                            and SortSorce in (0,1,2) and b.AreaID = '{0}' and a.DayTime='{1}' 
                                            Group By SortSorce,b.AreaName ORDER BY {2}", strAreaId, strDaytime, strOrderby);
                }
            }
            else
            {
                if (strAreaId.Substring(4, 2) == "00")
                {
                    sql = string.Format(@"
                                            SELECT b.AreaName,SortSorce as SortSource,
                                            SUM(isnull(PurchaseAmount,0)) AS PurchaseAmountTotal,
                                            SUM(isnull(PurchaseCount,0)) AS PurchaseCountTotal,
                                            SUM(isnull(WarehouseAmount,0)) AS WareHouseAmountTotal,
                                            SUM(isnull(WarehouseCount,0)) AS WareHouseCountTotal,
                                            SUM(isnull(DistributeAmount,0)) AS DistributeAmountTotal,
                                            case when SUM(isnull(WarehouseAmount,0))=0 then 0 
                                            when SUM(isnull(PurchaseAmount,0))=0 then 0
                                            when  SUM(isnull(WarehouseAmount,0)) / SUM  (isnull(PurchaseAmount,0))>1 then 1 
                                            else  convert(decimal(18,3),SUM(isnull(WarehouseAmount,0)) / SUM(isnull(PurchaseAmount,0))) end WarehouseRate,
                                            case when SUM(isnull(DistributeAmount,0))=0 then 0 when SUM(isnull(PurchaseAmount,0))=0 then 0
                                            when  SUM(isnull(DistributeAmount,0)) / SUM  (isnull(PurchaseAmount,0))>1 then 1 
                                            else   convert(decimal(18,3),SUM(isnull(DistributeAmount,0)) / SUM(isnull(PurchaseAmount,0))) end DistributeRate 
                                            FROM CACHE_STAT a inner join TB_Area b on  SUBSTRING(a.AreaID,1,4)+'00' = b.AreaID  
                                            and SortSorce ={3} and b.AreaID like '{0}' and a.DayTime = '{1}' 
                                            Group By SortSorce,b.AreaName ORDER BY {2}", strAreaId.Substring(0, 4) + "%", strDaytime, strOrderby, strSortSorce);
                }
                else
                {
                    sql = string.Format(@"
                                            SELECT b.AreaName,SortSorce as SortSource,
                                            SUM(isnull(PurchaseAmount,0)) AS PurchaseAmountTotal,
                                            SUM(isnull(PurchaseCount,0)) AS PurchaseCountTotal,
                                            SUM(isnull(WarehouseAmount,0)) AS WareHouseAmountTotal,
                                            SUM(isnull(WarehouseCount,0)) AS WareHouseCountTotal,
                                            SUM(isnull(DistributeAmount,0)) AS DistributeAmountTotal,
                                            case when SUM(isnull(WarehouseAmount,0))=0 then 0 
                                            when SUM(isnull(PurchaseAmount,0))=0 then 0
                                            when  SUM(isnull(WarehouseAmount,0)) / SUM  (isnull(PurchaseAmount,0))>1 then 1 
                                            else  convert(decimal(18,3),SUM(isnull(WarehouseAmount,0)) / SUM(isnull(PurchaseAmount,0))) end WarehouseRate,
                                            case when SUM(isnull(DistributeAmount,0))=0 then 0 when SUM(isnull(PurchaseAmount,0))=0 then 0
                                            when  SUM(isnull(DistributeAmount,0)) / SUM  (isnull(PurchaseAmount,0))>1 then 1 
                                            else  convert(decimal(18,3),SUM(isnull(DistributeAmount,0)) / SUM(isnull(PurchaseAmount,0))) end DistributeRate 
                                            FROM CACHE_STAT a inner join TB_Area b on  a.AreaID = b.AreaID  
                                            and SortSorce ={3} and b.AreaID = '{0}' and a.DayTime='{1}' 
                                            Group By SortSorce,b.AreaName ORDER BY {2}", strAreaId, strDaytime, strOrderby, strSortSorce);
                }
            }
            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
            des.CurrentPage = currentpage;
            totalrecord = des.TotalCount;
            return des.GetSpecailPage();
        }

        /// <summary>
        /// 
        /// </summary>
        /// sql语句入库率，配送率 计算方式修改 by：zhangxiong
        /// <param name="currentpage"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalrecord"></param>
        /// <param name="Mid"></param>
        /// <param name="Source"></param>
        /// <param name="orderby"></param>
        /// <param name="strAreaid"></param>
        /// <returns></returns>
        public DataTable ExcuteType(int currentpage, int pagesize, out int totalrecord, string Mid, string Source, string orderby, string strAreaid)
        {
            string sql = "";

            string sqlWhere = "AND C.AreaID LIKE '22%'";
            if (Source == "-1")
            {
                if (strAreaid == "")
                {

                    sql = string.Format(@"
                                            select SUBSTRING(T.AreaID,1,4)+'00' AreaID,max(T.AreaName) as areaname,C.SortSorce,
                                            SUM(C.PurchaseCount) PurchaseCountTotal,
                                            SUM(isnull(C.WarehouseCount,0)) WareHouseCountTotal,
                                            SUM(isnull(C.PurchaseAmount,0)) PurchaseAmountTotal,
                                            SUM(isnull(C.WarehouseAmount,0)) WareHouseAmountTotal,
                                            SUM(isnull(C.DistributeAmount,0)) DistributeAmountTotal,
                                            case when SUM(isnull(C.DistributeAmount,0)) = 0 then 0 
                                            when SUM(isnull(C.PurchaseAmount,0))=0 then 0 
                                            when CONVERT(decimal(18,3), SUM(isnull(C.DistributeAmount,0)) / SUM(isnull(C.PurchaseAmount,0)))>1 then 1 
                                            else CONVERT(decimal(18,3), SUM(isnull(C.DistributeAmount,0)) / SUM(isnull(C.PurchaseAmount,0))) end as DistributeRate,
                                            case when SUM(isnull(C.WarehouseAmount,0)) = 0 then 0 
                                            when SUM(isnull(C.PurchaseAmount,0)) = 0 then 0 
                                            when CONVERT(decimal(18,3), SUM(isnull(C.WarehouseAmount,0)) / SUM(isnull(C.PurchaseAmount,0)))>1 then 1
                                            else CONVERT(decimal(18,3), SUM(isnull(C.WarehouseAmount,0)) / SUM(isnull(C.PurchaseAmount,0))) end WareHouseRate 
                                            from CACHE_STAT C inner join TB_Area T 
                                            on SUBSTRING(C.AreaID,1,4)+'00' = T.AreaID " + sqlWhere + " and C.DayTime = '{0}' group by SUBSTRING(T.AreaID,1,4)+'00',C.SortSorce order by {1}", Mid, orderby);
                }
                else if (strAreaid.Substring(4, 2) == "00")
                {
                    sql = string.Format(@"
                                            select  T.AreaID,T.AreaName,C.SortSorce,
                                            SUM(isnull(C.PurchaseCount,0)) PurchaseCountTotal,
                                            SUM(isnull(C.WarehouseCount,0)) WareHouseCountTotal,
                                            SUM(isnull(C.PurchaseAmount,0)) PurchaseAmountTotal,
                                            SUM(isnull(C.WarehouseAmount,0)) WareHouseAmountTotal,
                                            SUM(isnull(C.DistributeAmount,0)) DistributeAmountTotal,
                                            case when SUM(isnull(C.DistributeAmount,0)) = 0 then 0 
                                            when SUM(isnull(C.PurchaseAmount,0))=0 then 0 
                                            when CONVERT(decimal(18,3), SUM(isnull(C.DistributeAmount,0)) / SUM(isnull(C.PurchaseAmount,0)))>1 then 1 
                                            else CONVERT(decimal(18,3), SUM(isnull(C.DistributeAmount,0)) / SUM(isnull(C.PurchaseAmount,0))) end as DistributeRate,
                                            case when SUM(isnull(C.WarehouseAmount,0)) = 0 then 0 
                                            when SUM(isnull(C.PurchaseAmount,0)) = 0 then 0 
                                            when CONVERT(decimal(18,3), SUM(isnull(C.WarehouseAmount,0)) / SUM(isnull(C.PurchaseAmount,0)))>1 then 1
                                            else CONVERT(decimal(18,3), SUM(isnull(C.WarehouseAmount,0)) / SUM(isnull(C.PurchaseAmount,0))) end WareHouseRate 
                                            from CACHE_STAT C inner join TB_Area T on C.AreaID = T.AreaID and C.DayTime = '{0}' and T.AreaID like '{2}%'  
                                            group by T.AreaID,T.AreaName,C.SortSorce order by {1}", Mid, orderby, strAreaid.Substring(0, 4));
                }
                else
                {

                    sql = string.Format(@"
                                            select  T.AreaID,T.AreaName,C.SortSorce,
                                            SUM(isnull(C.PurchaseCount,0)) PurchaseCountTotal,
                                            SUM(isnull(C.WarehouseCount,0)) WareHouseCountTotal,
                                            SUM(isnull(C.PurchaseAmount,0)) PurchaseAmountTotal,
                                            SUM(isnull(C.WarehouseAmount,0)) WareHouseAmountTotal,
                                            SUM(isnull(C.DistributeAmount,0)) DistributeAmountTotal,
                                            case when SUM(isnull(C.DistributeAmount,0)) = 0 then 0 
                                            when SUM(isnull(C.PurchaseAmount,0))=0 then 0 
                                            when CONVERT(decimal(18,3), SUM(isnull(C.DistributeAmount,0)) / SUM(isnull(C.PurchaseAmount,0)))>1 then 1 
                                            else CONVERT(decimal(18,3), SUM(isnull(C.DistributeAmount,0)) / SUM(isnull(C.PurchaseAmount,0))) end as DistributeRate,
                                            case when SUM(isnull(C.WarehouseAmount,0)) = 0 then 0 
                                            when SUM(isnull(C.PurchaseAmount,0)) = 0 then 0 
                                            when CONVERT(decimal(18,3), SUM(isnull(C.WarehouseAmount,0)) / SUM(isnull(C.PurchaseAmount,0)))>1 then 1
                                            else CONVERT(decimal(18,3), SUM(isnull(C.WarehouseAmount,0)) / SUM(isnull(C.PurchaseAmount,0))) end WareHouseRate 
                                            from CACHE_STAT C inner join TB_Area T on C.AreaID = T.AreaID and C.DayTime = '{0}' and T.AreaID='{2}'  
                                            group by T.AreaID,T.AreaName,C.SortSorce order by {1}", Mid, orderby, strAreaid);
                }

            }
            else
            {
                if (strAreaid == "")
                {
                    sql = string.Format(@"
                                            select SUBSTRING(T.AreaID,1,4)+'00' AreaID,T.AreaName,C.SortSorce,
                                            SUM(isnull(C.PurchaseCount,0)) PurchaseCountTotal,
                                            SUM(isnull(C.WarehouseCount,0)) WareHouseCountTotal,
                                            SUM(isnull(C.PurchaseAmount,0)) PurchaseAmountTotal,
                                            SUM(isnull(C.WarehouseAmount,0)) WareHouseAmountTotal,
                                            SUM(isnull(C.DistributeAmount,0)) DistributeAmountTotal,
                                            case when SUM(isnull(C.DistributeAmount,0)) = 0 then 0 
                                            when SUM(isnull(C.PurchaseAmount,0))=0 then 0 
                                            when CONVERT(decimal(18,3), SUM(isnull(C.DistributeAmount,0)) / SUM(isnull(C.PurchaseAmount,0)))>1 then 1 
                                            else CONVERT(decimal(18,3), SUM(isnull(C.DistributeAmount,0)) / SUM(isnull(C.PurchaseAmount,0))) end as DistributeRate,
                                            case when SUM(isnull(C.WarehouseAmount,0)) = 0 then 0 
                                            when SUM(isnull(C.PurchaseAmount,0)) = 0 then 0 
                                            when CONVERT(decimal(18,3), SUM(isnull(C.WarehouseAmount,0)) / SUM(isnull(C.PurchaseAmount,0)))>1 then 1
                                            else CONVERT(decimal(18,3), SUM(isnull(C.WarehouseAmount,0)) / SUM(isnull(C.PurchaseAmount,0))) end WareHouseRate 
                                            from CACHE_STAT C inner join TB_Area T on SUBSTRING(C.AreaID,1,4)+'00' = T.AreaID and C.DayTime = '{0}'and sortsorce in (0,1,2) and sortsorce={1}  
                                            group by SUBSTRING(T.AreaID,1,4)+'00',T.AreaName,C.SortSorce order by {2}", Mid, Source, orderby);
                }
                else if (strAreaid.Substring(4, 2) == "00")
                {
                    sql = string.Format(@"
                                            select T.AreaID,T.AreaName,C.SortSorce,
                                            SUM(isnull(C.PurchaseCount,0)) PurchaseCountTotal,
                                            SUM(isnull(C.WarehouseCount,0)) WareHouseCountTotal,
                                            SUM(isnull(C.PurchaseAmount,0)) PurchaseAmountTotal,
                                            SUM(isnull(C.WarehouseAmount,0)) WareHouseAmountTotal,
                                            SUM(isnull(C.DistributeAmount,0)) DistributeAmountTotal,
                                            case when SUM(isnull(C.DistributeAmount,0)) = 0 then 0 
                                            when SUM(isnull(C.PurchaseAmount,0))=0 then 0 
                                            when CONVERT(decimal(18,3), SUM(isnull(C.DistributeAmount,0)) / SUM(isnull(C.PurchaseAmount,0)))>1 then 1 
                                            else CONVERT(decimal(18,3), SUM(isnull(C.DistributeAmount,0)) / SUM(isnull(C.PurchaseAmount,0))) end as DistributeRate,
                                            case when SUM(isnull(C.WarehouseAmount,0)) = 0 then 0 
                                            when SUM(isnull(C.PurchaseAmount,0)) = 0 then 0 
                                            when CONVERT(decimal(18,3), SUM(isnull(C.WarehouseAmount,0)) / SUM(isnull(C.PurchaseAmount,0)))>1 then 1
                                            else CONVERT(decimal(18,3), SUM(isnull(C.WarehouseAmount,0)) / SUM(isnull(C.PurchaseAmount,0))) end WareHouseRate 
                                            from CACHE_STAT C inner join TB_Area T on C.AreaID = T.AreaID and C.DayTime = '{0}'and sortsorce in (0,1,2) and sortsorce={1}  and T.AreaID like '{3}%' 
                                            group by  T.AreaID,T.AreaName,C.SortSorce order by {2}", Mid, Source, orderby, strAreaid.Substring(0, 4));
                }
                else
                {

                    sql = string.Format(@"
                                            select  T.AreaID,T.AreaName,C.SortSorce,
                                            SUM(isnull(C.PurchaseCount,0)) PurchaseCountTotal,
                                            SUM(isnull(C.WarehouseCount,0)) WareHouseCountTotal,
                                            SUM(isnull(C.PurchaseAmount,0)) PurchaseAmountTotal,
                                            SUM(isnull(C.WarehouseAmount,0)) WareHouseAmountTotal,
                                            SUM(isnull(C.DistributeAmount,0)) DistributeAmountTotal,
                                            case when SUM(isnull(C.DistributeAmount,0)) = 0 then 0 
                                            when SUM(isnull(C.PurchaseAmount,0))=0 then 0 
                                            when CONVERT(decimal(18,3), SUM(isnull(C.DistributeAmount,0)) / SUM(isnull(C.PurchaseAmount,0)))>1 then 1 
                                            else CONVERT(decimal(18,3), SUM(isnull(C.DistributeAmount,0)) / SUM(isnull(C.PurchaseAmount,0))) end as DistributeRate,
                                            case when SUM(isnull(C.WarehouseAmount,0)) = 0 then 0 
                                            when SUM(isnull(C.PurchaseAmount,0)) = 0 then 0 
                                            when CONVERT(decimal(18,3), SUM(isnull(C.WarehouseAmount,0)) / SUM(isnull(C.PurchaseAmount,0)))>1 then 1
                                            else CONVERT(decimal(18,3), SUM(isnull(C.WarehouseAmount,0)) / SUM(isnull(C.PurchaseAmount,0))) end WareHouseRate 
                                             from CACHE_STAT C inner join TB_Area T on C.AreaID = T.AreaID and C.DayTime = '{0}' and T.AreaID='{2}' and sortsorce in (0,1,2)  and sortsorce={4}  
                                            group by T.AreaID,T.AreaName,C.SortSorce order by {1}", Mid, orderby, strAreaid, Source);
                }
            }


            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
            des.CurrentPage = currentpage;
            totalrecord = des.TotalCount;
            return des.GetSpecailPage();
        }

        /// <summary>
        /// 地区
        /// 所有药品信息
        /// by张雄  2011.5.12
        /// </summary>
        /// <param name="where">sql where条件</param>
        /// <param name="currentpage">当前页</param>
        /// <param name="pagesize">每页的行数</param>
        /// <param name="totalrecord">参数</param>
        /// <returns>所有药品信息</returns>
        public DataTable ExecuteDetailsArea(string where, string order, int currentpage, int pagesize, out int totalrecord)
        {
            string sql = string.Format(@"
                                            SELECT B.SortSource , ProductName,MedicineModelName,Outlookc,Factor,Unit,CompanyName_SC,
                                            SortName,CompanyName_TB,GoodsName,Pack,QualityLevel,ProcurePrice,B.ProcureCatalogID,
                                            PurchaseCountTotal, PurchaseAmountTotal, WareHouseCountTotal, WareHouseRate, 
                                            WareHouseAmountTotal ,DistributeAmountTotal,DistributeRate FROM View_ProcureCatalog_TB INNER JOIN (
                                            SELECT ProcureCatalogID,SortSource,
                                            SUM(isnull(PurchaseAmount,0)) AS PurchaseAmountTotal,
                                            SUM(isnull(WarehouseAmount,0)) AS WareHouseAmountTotal ,
                                            sum(isnull(DistributeAmount,0)) as DistributeAmountTotal,
                                            SUM(isnull(PurchaseCount,0)) AS PurchaseCountTotal,
                                            SUM(isnull(WarehouseCount,0)) AS WareHouseCountTotal,	
                                            Case	when sum(isnull(DistributeAmount,0))=0 then 0	
                                            when sum(isnull(PurchaseAmount,0))=0 then 0	
                                            when CONVERT(numeric(18,3),CONVERT(float, SUM(isnull(DistributeAmount,0))) / SUM(isnull(PurchaseAmount,0)))>1 then 1	
                                            else	CONVERT(decimal(18,3),SUM(isnull(DistributeAmount,0)) / SUM(isnull(PurchaseAmount,0))) end as DistributeRate,
                                            Case	when sum(isnull(WareHouseCount,0))=0 then 0	
                                            when sum(isnull(PurchaseAmount,0))=0 then 0 
                                            when CONVERT(numeric(18,3),CONVERT(float, SUM(isnull(WarehouseAmount,0))) / SUM(isnull(PurchaseAmount,0)))>1 then 1	
                                            else CONVERT(numeric(18,3),CONVERT(float, SUM(isnull(WarehouseAmount,0))) / SUM(isnull(PurchaseAmount,0))) end AS WareHouseRate	
                                            FROM CACHE_STAT_Goods WHERE 1=1 {0} GROUP BY ProcureCatalogID,SortSource
                                            ) AS B ON View_ProcureCatalog_TB.ProcureCatalogID = B.ProcureCatalogID ORDER BY {1}", where, order);
            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
            des.CurrentPage = currentpage;
            totalrecord = des.TotalCount;
            return des.GetSpecailPage();
        }
        public DataTable ExecuteDetails(int currentpage, int pagesize, out int totalrecord)
        {
            if (QueryObject == null)
            {
                throw new ArgumentNullException("QueryObject 必须赋值后才能执行");
            }
            string sql = HZ.Supervise.DAL.BuildSql.GetSql<Model.SuperviseQuery>(QueryObject);

            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);

            des.CurrentPage = currentpage;

            totalrecord = des.TotalCount;

            return des.GetSpecailPage();
        }

        public DataTable ExecuteAreaID(string AreaID, int currentpage, int pagesize, out int totalrecord)
        {
            string sql = string.Format("SELECT B.AreaID,B.SortSource,PurchaseCountTotal, PurchaseAmountTotal, DistributeRate, WareHouseCountTotal, WareHouseRate, WareHouseAmountTotal,AreaName FROM (SELECT AreaID,SortSource AS SortSource,Case  WHEN SUM(DistributeCount)=0 THEN 0 WHEN SUM(PurchaseCount)=0 THEN 0  ELSE  CONVERT(FLOAT,SUM(DistributeCount*Price)) / SUM(PurchaseAmount) END   AS DistributeRate,SUM(PurchaseAmount) AS PurchaseAmountTotal,SUM(PurchaseCount) AS PurchaseCountTotal,SUM(WarehouseCount) AS WareHouseCountTotal,Case when sum(WareHouseCount)=0 then 0 when sum(PurchaseCount)=0 then 0 else CONVERT(decimal(18,3), CONVERT(FLOAT,SUM(WarehouseCount)) / SUM(PurchaseCount)) end AS WareHouseRate,SUM(WarehouseAmount) AS WareHouseAmountTotal FROM View_Supervise WHERE 1=1 {0} and SortSource !=0 and SortSource!=1"
                                        + " AND PlanTime >= '2010-3-31' AND PlanTime <= '2011-4-21' GROUP BY AreaID,SortSource) AS B  INNER JOIN TB_Area ON TB_Area.AreaID = B.AreaID ORDER BY SortSource ASC", AreaID);

            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
            des.CurrentPage = currentpage;
            totalrecord = des.TotalCount;
            return des.GetSpecailPage();
        }
        /// <summary>
        /// 曹鹏 
        /// 2011年5月5日10:23:53
        /// 统计全市的总数据
        /// </summary>
        /// <param name="where1">查询条件</param>
        /// <returns>返回</returns>
        public DataTable ExecuteAreaIDByCity(string where1)//modify by 曹鹏 2011年5月5日9:12:36 根据县区查询数据 
        {

            string sql = string.Format("  select B.AreaName,A.* from (select Substring(AreaID,1,4) as AreaId, SortSorce as SortSource,SUM(PurchaseAmount) as PurchaseAmountTotal,SUM(DistributeAmount) as DistributeAmountTotal,SUM(WarehouseAmount) as WarehouseAmountTotal,case when SUM(DistributeAmount)=0 then 0 when SUM(PurchaseAmount)=0 then 0 when CONVERT(decimal(18,3),CONVERT(float, SUM(DistributeAmount)) / CONVERT(float, SUM(PurchaseAmount)))>1 then 1 else CONVERT(decimal(18,3),CONVERT(float, SUM(DistributeAmount)) / CONVERT(float, SUM(PurchaseAmount))) end as DistributeRate,case when SUM(WarehouseAmount)=0 then 0 when SUM(PurchaseAmount)=0 then 0 when CONVERT(decimal(18,3),CONVERT(float, SUM(WarehouseAmount)) / CONVERT(float, SUM(PurchaseAmount)))>1 then 1 else CONVERT(decimal(18,3),CONVERT(float, SUM(WarehouseAmount)) / CONVERT(float, SUM(PurchaseAmount))) end as WareHouseRate from dbo.CACHE_STAT where 1=1 {0} group by SortSorce,Substring(AreaID,1,4)) A inner join TB_Area B on B.AreaID = A.areaid+'00' order by B.AreaName ", where1);

            return HZ.Data.DbHelperFactory.Create().ExecuteTable(sql);

        }
        /// <summary>
        /// 曹鹏
        /// 2011年5月5日10:24:21
        /// 统计单个县区的数据
        /// </summary>
        /// <param name="where2">查询条件</param>
        /// <returns>返回</returns>
        public DataTable ExecuteAreaIDOfCountry(string where2)//modify by 曹鹏 2011年5月5日9:12:36 根据县区查询数据 
        {
            string sql = string.Format("select AreaName,AreaId, SortSorce as SortSource,SUM(PurchaseAmount) as PurchaseAmountTotal,SUM(DistributeAmount) as DistributeAmountTotal,SUM(WarehouseAmount) as WarehouseAmountTotal,case when SUM(DistributeAmount)=0 then 0 when SUM(PurchaseAmount)=0 then 0 when CONVERT(decimal(18,3),CONVERT(float, SUM(DistributeAmount)) / CONVERT(float, SUM(PurchaseAmount)))>1 then 1 else CONVERT(decimal(18,3),CONVERT(float, SUM(DistributeAmount)) / CONVERT(float, SUM(PurchaseAmount))) end as DistributeRate,case when SUM(WarehouseAmount)=0 then 0 when SUM(PurchaseAmount)=0 then 0 when CONVERT(decimal(18,3),CONVERT(float, SUM(WarehouseAmount)) / CONVERT(float, SUM(PurchaseAmount)))>1 then 1 else CONVERT(decimal(18,3),CONVERT(float, SUM(WarehouseAmount)) / CONVERT(float, SUM(PurchaseAmount))) end as WareHouseRate from dbo.CACHE_STAT where 1=1 {0} group by AreaName,SortSorce,AreaId order by AreaName", where2);

            return HZ.Data.DbHelperFactory.Create().ExecuteTable(sql);
        }
        /// <summary>
        /// 产品配送率 //i=0 表示地区分开的数据信息 ，i=1 表示不分地区汇总后的数据
        /// </summary>
        /// <param name="proce"></param>
        /// <param name="currentpage"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalrecord"></param>
        /// <returns></returns>
        public DataTable getGoodsPSLList(int i, string where, string ordrby, int currentpage, int pagesize, out int totalrecord)
        {
            return HZ.Supervise.DAL.Supervise.getGoodsPSLList(i, where, ordrby, currentpage, pagesize, out totalrecord);
        }

        /// <summary>
        /// 医院药品汇总     //  
        /// JOHN
        /// </summary>
        /// <param name="i"></param>
        /// <param name="where"></param>
        /// <param name="ordrby"></param>
        /// <param name="currentpage"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalrecord"></param>
        /// <returns></returns>
        public System.Data.DataTable GetHospitalGoodsList(int i, string where, string ordrby, int currentpage, int pagesize, out int totalrecord)
        {
            return HZ.Supervise.DAL.Supervise.GetHospitalGoodsList(i, where, ordrby, currentpage, pagesize, out totalrecord);
        }

        /// <summary>
        /// 医院药品汇总     //  
        /// JOHN
        /// </summary>
        /// <param name="i"></param>
        /// <param name="where"></param>
        /// <param name="ordrby"></param>
        /// <param name="currentpage"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalrecord"></param>
        /// <returns></returns>
        public System.Data.DataTable GetHospitalGoods(int i, string where, string ordrby, int currentpage, int pagesize, out int totalrecord)
        {
            return HZ.Supervise.DAL.Supervise.GetHospitalGoods(i, where, ordrby, currentpage, pagesize, out totalrecord);
        }

        /// <summary>
        /// 按药品统计  by张雄
        /// </summary>
        /// <param name="currentpage">当前页</param>
        /// <param name="pagesize">每页显示的行数</param>
        /// <param name="totalrecord"></param>
        /// <returns>药品统计信息</returns>
        public DataTable GetGoodsInfoLList(string timeFrom, string timeTo, string timeFromH, string timeToH, string timeFromP, string timeToP, int currentpage, int pagesize, out int totalrecord)
        {
            #region 时间格式转换

            StringBuilder sb = new StringBuilder();
            StringBuilder sb0 = new StringBuilder();
            StringBuilder sb1 = new StringBuilder();

            //中心提交配送时间
            if (!string.IsNullOrEmpty(timeFrom.Trim()))
            {
                sb.AppendFormat(" and CenSubmiitTime >= '{0}' ", timeFrom.Trim() + "-01 00:00:00:000");

            }
            //timeFrom.Trim().Remove(4, 1)
            if (!string.IsNullOrEmpty(timeTo.Trim()))
            {
                int year, month, days = 0;
                year = Convert.ToInt32(timeTo.Trim().Substring(0, 4));
                month = Convert.ToInt32(timeTo.Trim().Substring(5, 2));

                //得到当前月的天数信息。 
                switch (month)
                {
                    case 1:
                        days = 31;
                        break;
                    case 2:
                        if (DateTime.IsLeapYear(year))
                        {
                            //闰年二月为   29   天 
                            days = 29;
                        }
                        else
                        {
                            //闰年二月为   28   天 
                            days = 28;
                        }

                        break;
                    case 3:
                        days = 31;
                        break;
                    case 4:
                        days = 30;
                        break;
                    case 5:
                        days = 31;
                        break;
                    case 6:
                        days = 30;
                        break;
                    case 7:
                        days = 31;
                        break;
                    case 8:
                        days = 31;
                        break;
                    case 9:
                        days = 30;
                        break;
                    case 10:
                        days = 31;
                        break;
                    case 11:
                        days = 30;
                        break;
                    case 12:
                        days = 31;
                        break;
                }

                timeTo = year + "-" + month + "-" + days + " 00:00:00:000";
                sb.AppendFormat(" and CenSubmiitTime <= '{0}'", timeTo.Trim());

            }

            //企业配送时间
            if (!string.IsNullOrEmpty(timeFromP.Trim()))
            {
                sb0.AppendFormat(" and DistributeTime >= '{0}' ", timeFromP.Trim() + "-01 00:00:00:000");

            }
            if (!string.IsNullOrEmpty(timeToP.Trim()))
            {
                int year, month, days = 0;
                year = Convert.ToInt32(timeToP.Trim().Substring(0, 4));
                month = Convert.ToInt32(timeToP.Trim().Substring(5, 2));

                //得到当前月的天数信息。 
                switch (month)
                {
                    case 1:
                        days = 31;
                        break;
                    case 2:
                        if (DateTime.IsLeapYear(year))
                        {
                            //闰年二月为   29   天 
                            days = 29;
                        }
                        else
                        {
                            //闰年二月为   28   天 
                            days = 28;
                        }

                        break;
                    case 3:
                        days = 31;
                        break;
                    case 4:
                        days = 30;
                        break;
                    case 5:
                        days = 31;
                        break;
                    case 6:
                        days = 30;
                        break;
                    case 7:
                        days = 31;
                        break;
                    case 8:
                        days = 31;
                        break;
                    case 9:
                        days = 30;
                        break;
                    case 10:
                        days = 31;
                        break;
                    case 11:
                        days = 30;
                        break;
                    case 12:
                        days = 31;
                        break;
                }

                timeToP = year + "-" + month + "-" + days + " 00:00:00:000";
                sb0.AppendFormat(" and DistributeTime <= '{0}'", timeToP.Trim());

            }



            //医院入库时间
            if (!string.IsNullOrEmpty(timeFromH.Trim()))
            {
                sb1.AppendFormat(" and warehousetime >= '{0}' ", timeFromH.Trim() + "-01 00:00:00:000");

            }
            //timeFrom.Trim().Remove(4, 1)
            if (!string.IsNullOrEmpty(timeToH.Trim()))
            {
                int year, month, days = 0;
                year = Convert.ToInt32(timeToH.Trim().Substring(0, 4));
                month = Convert.ToInt32(timeToH.Trim().Substring(5, 2));

                //得到当前月的天数信息。 
                switch (month)
                {
                    case 1:
                        days = 31;
                        break;
                    case 2:
                        if (DateTime.IsLeapYear(year))
                        {
                            //闰年二月为   29   天 
                            days = 29;
                        }
                        else
                        {
                            //闰年二月为   28   天 
                            days = 28;
                        }

                        break;
                    case 3:
                        days = 31;
                        break;
                    case 4:
                        days = 30;
                        break;
                    case 5:
                        days = 31;
                        break;
                    case 6:
                        days = 30;
                        break;
                    case 7:
                        days = 31;
                        break;
                    case 8:
                        days = 31;
                        break;
                    case 9:
                        days = 30;
                        break;
                    case 10:
                        days = 31;
                        break;
                    case 11:
                        days = 30;
                        break;
                    case 12:
                        days = 31;
                        break;
                }

                timeToH = year + "-" + month + "-" + days + " 00:00:00:000";
                sb1.AppendFormat(" and warehousetime <= '{0}'", timeToH.Trim());

            }
            #endregion

            return HZ.Supervise.DAL.Supervise.GetGoodsInfoLList(sb.ToString(), sb1.ToString(), sb0.ToString(), currentpage, pagesize, out totalrecord);
        }

        /// <summary>
        /// 按企业统计-配送 by张雄
        /// </summary>
        /// <param name="currentpage">当前页</param>
        /// <param name="pagesize">每页显示的行数</param>
        /// <param name="totalrecord"></param>
        /// <returns>企业统计信息</returns>
        public DataTable GetCompanyInfo_PS(string timeFrom, string timeTo, string timeFromH, string timeToH, string timeFromP, string timeToP, int currentpage, int pagesize, out int totalrecord)
        {
            #region 时间格式转换

            StringBuilder sb = new StringBuilder();

            StringBuilder sb0 = new StringBuilder();

            StringBuilder sb1 = new StringBuilder();

            if (!string.IsNullOrEmpty(timeFrom.Trim()))
            {
                sb.AppendFormat(" and CenSubmiitTime >= '{0}' ", timeFrom.Trim() + "-01 00:00:00:000");

            }

            if (!string.IsNullOrEmpty(timeTo.Trim()))
            {
                int year, month, days = 0;
                year = Convert.ToInt32(timeTo.Trim().Substring(0, 4));
                month = Convert.ToInt32(timeTo.Trim().Substring(5, 2));

                //得到当前月的天数信息。 
                switch (month)
                {
                    case 1:
                        days = 31;
                        break;
                    case 2:
                        if (DateTime.IsLeapYear(year))
                        {
                            //闰年二月为   29   天 
                            days = 29;
                        }
                        else
                        {
                            //闰年二月为   28   天 
                            days = 28;
                        }

                        break;
                    case 3:
                        days = 31;
                        break;
                    case 4:
                        days = 30;
                        break;
                    case 5:
                        days = 31;
                        break;
                    case 6:
                        days = 30;
                        break;
                    case 7:
                        days = 31;
                        break;
                    case 8:
                        days = 31;
                        break;
                    case 9:
                        days = 30;
                        break;
                    case 10:
                        days = 31;
                        break;
                    case 11:
                        days = 30;
                        break;
                    case 12:
                        days = 31;
                        break;
                }

                timeTo = year + "-" + month + "-" + days + " 00:00:00:000";
                sb.AppendFormat(" and CenSubmiitTime <= '{0}'", timeTo.Trim());

            }

            //企业配送时间
            if (!string.IsNullOrEmpty(timeFromP.Trim()))
            {
                sb0.AppendFormat(" and DistributeTime >= '{0}' ", timeFromP.Trim() + "-01 00:00:00:000");

            }

            if (!string.IsNullOrEmpty(timeToP.Trim()))
            {
                int year, month, days = 0;
                year = Convert.ToInt32(timeToP.Trim().Substring(0, 4));
                month = Convert.ToInt32(timeToP.Trim().Substring(5, 2));

                //得到当前月的天数信息。 
                switch (month)
                {
                    case 1:
                        days = 31;
                        break;
                    case 2:
                        if (DateTime.IsLeapYear(year))
                        {
                            //闰年二月为   29   天 
                            days = 29;
                        }
                        else
                        {
                            //闰年二月为   28   天 
                            days = 28;
                        }

                        break;
                    case 3:
                        days = 31;
                        break;
                    case 4:
                        days = 30;
                        break;
                    case 5:
                        days = 31;
                        break;
                    case 6:
                        days = 30;
                        break;
                    case 7:
                        days = 31;
                        break;
                    case 8:
                        days = 31;
                        break;
                    case 9:
                        days = 30;
                        break;
                    case 10:
                        days = 31;
                        break;
                    case 11:
                        days = 30;
                        break;
                    case 12:
                        days = 31;
                        break;
                }

                timeToP = year + "-" + month + "-" + days + " 00:00:00:000";
                sb0.AppendFormat(" and DistributeTime <= '{0}'", timeToP.Trim());

            }

            //医院入库时间
            if (!string.IsNullOrEmpty(timeFromH.Trim()))
            {
                sb1.AppendFormat(" and warehousetime >= '{0}' ", timeFromH.Trim() + "-01 00:00:00:000");

            }

            if (!string.IsNullOrEmpty(timeToH.Trim()))
            {
                int year, month, days = 0;
                year = Convert.ToInt32(timeToH.Trim().Substring(0, 4));
                month = Convert.ToInt32(timeToH.Trim().Substring(5, 2));

                //得到当前月的天数信息。 
                switch (month)
                {
                    case 1:
                        days = 31;
                        break;
                    case 2:
                        if (DateTime.IsLeapYear(year))
                        {
                            //闰年二月为   29   天 
                            days = 29;
                        }
                        else
                        {
                            //闰年二月为   28   天 
                            days = 28;
                        }

                        break;
                    case 3:
                        days = 31;
                        break;
                    case 4:
                        days = 30;
                        break;
                    case 5:
                        days = 31;
                        break;
                    case 6:
                        days = 30;
                        break;
                    case 7:
                        days = 31;
                        break;
                    case 8:
                        days = 31;
                        break;
                    case 9:
                        days = 30;
                        break;
                    case 10:
                        days = 31;
                        break;
                    case 11:
                        days = 30;
                        break;
                    case 12:
                        days = 31;
                        break;
                }

                timeToH = year + "-" + month + "-" + days + " 23:59:59:000";
                sb1.AppendFormat(" and warehousetime <= '{0}'", timeToH.Trim());

            }

            #endregion

            return HZ.Supervise.DAL.Supervise.GetCompanyInfo_PS(sb.ToString(), sb1.ToString(), sb0.ToString(), currentpage, pagesize, out totalrecord);
        }

        /// <summary>
        /// 按企业统计-生产 by张雄
        /// </summary>
        /// <param name="currentpage">当前页</param>
        /// <param name="pagesize">每页显示的行数</param>
        /// <param name="totalrecord"></param>
        /// <returns>企业统计信息</returns>
        public DataTable GetCompanyInfo_SC(string timeFrom, string timeTo, string timeFromH, string timeToH, string timeFromP, string timeToP, int currentpage, int pagesize, out int totalrecord)
        {
            #region 时间格式转换

            StringBuilder sb = new StringBuilder();

            StringBuilder sb0 = new StringBuilder();

            StringBuilder sb1 = new StringBuilder();

            //中心提交配送时间
            if (!string.IsNullOrEmpty(timeFrom.Trim()))
            {
                sb.AppendFormat(" and CenSubmiitTime >= '{0}' ", timeFrom.Trim() + "-01 00:00:00:000");

            }

            if (!string.IsNullOrEmpty(timeTo.Trim()))
            {
                int year, month, days = 0;
                year = Convert.ToInt32(timeTo.Trim().Substring(0, 4));
                month = Convert.ToInt32(timeTo.Trim().Substring(5, 2));

                //得到当前月的天数信息。 
                switch (month)
                {
                    case 1:
                        days = 31;
                        break;
                    case 2:
                        if (DateTime.IsLeapYear(year))
                        {
                            //闰年二月为   29   天 
                            days = 29;
                        }
                        else
                        {
                            //闰年二月为   28   天 
                            days = 28;
                        }

                        break;
                    case 3:
                        days = 31;
                        break;
                    case 4:
                        days = 30;
                        break;
                    case 5:
                        days = 31;
                        break;
                    case 6:
                        days = 30;
                        break;
                    case 7:
                        days = 31;
                        break;
                    case 8:
                        days = 31;
                        break;
                    case 9:
                        days = 30;
                        break;
                    case 10:
                        days = 31;
                        break;
                    case 11:
                        days = 30;
                        break;
                    case 12:
                        days = 31;
                        break;
                }

                timeTo = year + "-" + month + "-" + days + " 23:59:59:000";
                sb.AppendFormat(" and CenSubmiitTime <= '{0}'", timeTo.Trim());

            }

            //配送企业配送时间
            if (!string.IsNullOrEmpty(timeFromP.Trim()))
            {
                sb0.AppendFormat(" and DistributeTime >= '{0}' ", timeFromP.Trim() + "-01 00:00:00:000");

            }

            if (!string.IsNullOrEmpty(timeToP.Trim()))
            {
                int year, month, days = 0;
                year = Convert.ToInt32(timeToP.Trim().Substring(0, 4));
                month = Convert.ToInt32(timeToP.Trim().Substring(5, 2));

                //得到当前月的天数信息。 
                switch (month)
                {
                    case 1:
                        days = 31;
                        break;
                    case 2:
                        if (DateTime.IsLeapYear(year))
                        {
                            //闰年二月为   29   天 
                            days = 29;
                        }
                        else
                        {
                            //闰年二月为   28   天 
                            days = 28;
                        }

                        break;
                    case 3:
                        days = 31;
                        break;
                    case 4:
                        days = 30;
                        break;
                    case 5:
                        days = 31;
                        break;
                    case 6:
                        days = 30;
                        break;
                    case 7:
                        days = 31;
                        break;
                    case 8:
                        days = 31;
                        break;
                    case 9:
                        days = 30;
                        break;
                    case 10:
                        days = 31;
                        break;
                    case 11:
                        days = 30;
                        break;
                    case 12:
                        days = 31;
                        break;
                }

                timeToP = year + "-" + month + "-" + days + " 23:59:59:000";
                sb0.AppendFormat(" and DistributeTime <= '{0}'", timeToP.Trim());

            }

            //医院入库时间

            if (!string.IsNullOrEmpty(timeFromH.Trim()))
            {
                sb1.AppendFormat(" and warehousetime >= '{0}' ", timeFromH.Trim() + "-01 00:00:00:000");

            }

            if (!string.IsNullOrEmpty(timeToH.Trim()))
            {
                int year, month, days = 0;
                year = Convert.ToInt32(timeToH.Trim().Substring(0, 4));
                month = Convert.ToInt32(timeToH.Trim().Substring(5, 2));

                //得到当前月的天数信息。 
                switch (month)
                {
                    case 1:
                        days = 31;
                        break;
                    case 2:
                        if (DateTime.IsLeapYear(year))
                        {
                            //闰年二月为   29   天 
                            days = 29;
                        }
                        else
                        {
                            //闰年二月为   28   天 
                            days = 28;
                        }

                        break;
                    case 3:
                        days = 31;
                        break;
                    case 4:
                        days = 30;
                        break;
                    case 5:
                        days = 31;
                        break;
                    case 6:
                        days = 30;
                        break;
                    case 7:
                        days = 31;
                        break;
                    case 8:
                        days = 31;
                        break;
                    case 9:
                        days = 30;
                        break;
                    case 10:
                        days = 31;
                        break;
                    case 11:
                        days = 30;
                        break;
                    case 12:
                        days = 31;
                        break;
                }

                timeToH = year + "-" + month + "-" + days + " 23:59:59:000";
                sb1.AppendFormat(" and warehousetime <= '{0}'", timeToH.Trim());

            }
            #endregion

            return HZ.Supervise.DAL.Supervise.GetCompanyInfo_SC(sb.ToString(), sb1.ToString(), sb0.ToString(), currentpage, pagesize, out totalrecord);
        }

        /// <summary>
        /// 按地区统计 by张雄
        /// </summary>
        /// <param name="currentpage">当前页</param>
        /// <param name="pagesize">每页显示的行数</param>
        /// <param name="totalrecord"></param>
        /// <returns>企业统计信息</returns>
        public DataTable GetAreaInfoList(string timeFrom, string timeTo, string timeFromH, string timeToH, string timeFromP, string timeToP, int currentpage, int pagesize, out int totalrecord)
        {
            #region 时间格式转换

            StringBuilder sb = new StringBuilder();

            StringBuilder sb0 = new StringBuilder();

            StringBuilder sb1 = new StringBuilder();

            //中心提交配送时间
            if (!string.IsNullOrEmpty(timeFrom.Trim()))
            {
                sb.AppendFormat(" and CenSubmiitTime >= '{0}' ", timeFrom.Trim() + "-01 00:00:00:000");

            }

            if (!string.IsNullOrEmpty(timeTo.Trim()))
            {
                int year, month, days = 0;
                year = Convert.ToInt32(timeTo.Trim().Substring(0, 4));
                month = Convert.ToInt32(timeTo.Trim().Substring(5, 2));

                //得到当前月的天数信息。 
                switch (month)
                {
                    case 1:
                        days = 31;
                        break;
                    case 2:
                        if (DateTime.IsLeapYear(year))
                        {
                            //闰年二月为   29   天 
                            days = 29;
                        }
                        else
                        {
                            //闰年二月为   28   天 
                            days = 28;
                        }

                        break;
                    case 3:
                        days = 31;
                        break;
                    case 4:
                        days = 30;
                        break;
                    case 5:
                        days = 31;
                        break;
                    case 6:
                        days = 30;
                        break;
                    case 7:
                        days = 31;
                        break;
                    case 8:
                        days = 31;
                        break;
                    case 9:
                        days = 30;
                        break;
                    case 10:
                        days = 31;
                        break;
                    case 11:
                        days = 30;
                        break;
                    case 12:
                        days = 31;
                        break;
                }

                timeTo = year + "-" + month + "-" + days + " 00:00:00:000";
                sb.AppendFormat(" and CenSubmiitTime <= '{0}'", timeTo.Trim());
            }

            //企业配送时间
            if (!string.IsNullOrEmpty(timeFromP.Trim()))
            {
                sb0.AppendFormat(" and DistributeTime >= '{0}' ", timeFromP.Trim() + "-01 00:00:00:000");

            }

            if (!string.IsNullOrEmpty(timeToP.Trim()))
            {
                int year, month, days = 0;
                year = Convert.ToInt32(timeToP.Trim().Substring(0, 4));
                month = Convert.ToInt32(timeToP.Trim().Substring(5, 2));

                //得到当前月的天数信息。 
                switch (month)
                {
                    case 1:
                        days = 31;
                        break;
                    case 2:
                        if (DateTime.IsLeapYear(year))
                        {
                            //闰年二月为   29   天 
                            days = 29;
                        }
                        else
                        {
                            //闰年二月为   28   天 
                            days = 28;
                        }

                        break;
                    case 3:
                        days = 31;
                        break;
                    case 4:
                        days = 30;
                        break;
                    case 5:
                        days = 31;
                        break;
                    case 6:
                        days = 30;
                        break;
                    case 7:
                        days = 31;
                        break;
                    case 8:
                        days = 31;
                        break;
                    case 9:
                        days = 30;
                        break;
                    case 10:
                        days = 31;
                        break;
                    case 11:
                        days = 30;
                        break;
                    case 12:
                        days = 31;
                        break;
                }

                timeToP = year + "-" + month + "-" + days + " 00:00:00:000";
                sb0.AppendFormat(" and DistributeTime <= '{0}'", timeToP.Trim());
            }

            //医院入库时间
            if (!string.IsNullOrEmpty(timeFromH.Trim()))
            {
                sb1.AppendFormat(" and warehousetime >= '{0}' ", timeFromH.Trim() + "-01 00:00:00:000");

            }

            if (!string.IsNullOrEmpty(timeToH.Trim()))
            {
                int year, month, days = 0;
                year = Convert.ToInt32(timeToH.Trim().Substring(0, 4));
                month = Convert.ToInt32(timeToH.Trim().Substring(5, 2));

                //得到当前月的天数信息。 
                switch (month)
                {
                    case 1:
                        days = 31;
                        break;
                    case 2:
                        if (DateTime.IsLeapYear(year))
                        {
                            //闰年二月为   29   天 
                            days = 29;
                        }
                        else
                        {
                            //闰年二月为   28   天 
                            days = 28;
                        }

                        break;
                    case 3:
                        days = 31;
                        break;
                    case 4:
                        days = 30;
                        break;
                    case 5:
                        days = 31;
                        break;
                    case 6:
                        days = 30;
                        break;
                    case 7:
                        days = 31;
                        break;
                    case 8:
                        days = 31;
                        break;
                    case 9:
                        days = 30;
                        break;
                    case 10:
                        days = 31;
                        break;
                    case 11:
                        days = 30;
                        break;
                    case 12:
                        days = 31;
                        break;
                }

                timeToH = year + "-" + month + "-" + days + " 23:59:59:000";
                sb1.AppendFormat(" and warehousetime <= '{0}'", timeToH.Trim());
            }
            #endregion

            return HZ.Supervise.DAL.Supervise.GetAreaInfoList(sb.ToString(), sb1.ToString(), sb0.ToString(), currentpage, pagesize, out totalrecord);
        }

        /// <summary>
        /// 各市配送率汇总
        /// </summary>
        /// <param name="hosproce"></param>
        /// <param name="currentpage"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalrecord"></param>
        /// <returns></returns>
        public DataTable getAreaSHIlIST(string orderby, string Area, string source)
        {
            return HZ.Supervise.DAL.Supervise.getAreaSHIlIST(orderby, Area, source);
        }
        /// <summary>
        /// 获取图标汇总个数(采购)
        /// </summary>
        /// <returns></returns>
        public DataTable GetSuperviseChart1(object sqlwhere)
        {
            return DAL.Supervise.GetSuperviseChart1(sqlwhere);
        }
        /// <summary>
        /// 配送企业配送率查询
        /// </summary>
        /// <param name="companyname"></param>
        /// <param name="currentpage"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalrecord"></param>
        /// <returns></returns>
        public DataTable ExecutePagePSL(string companyname, string orderby, int currentpage, int pagesize, out int totalrecord)
        {
            string where = "";

            if (!string.IsNullOrEmpty(companyname))
            {
                where += "and CompanyName like '%" + companyname + "%'";
            }

            string sql = @"select *,CASE WHEN ISNULL(PurchaseAmount,0)=0 THEN 0 ELSE  DistributeAmount/PurchaseAmount END DistributeRate,CASE WHEN ISNULL(PurchaseAmount,0)=0 THEN 0 ELSE WarehouseAmount/PurchaseAmount END WarehouseRate   from CACHE_STAT where 1=1  order by " + orderby;

            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);

            des.CurrentPage = currentpage;

            totalrecord = des.TotalCount;

            return des.GetSpecailPage();
        }

        /// <summary>
        /// 获取图标汇总个数(配送)
        /// </summary>
        /// <returns></returns>
        public DataTable GetSuperviseChart2(object sqlwhere)
        {
            return DAL.Supervise.GetSuperviseChart2(sqlwhere);
        }

        /// <summary>
        /// 生产企业图标汇总
        /// </summary>
        /// <returns>返回数据集</returns>
        public DataTable GetSuperviseCompanySC_Chart()
        {
            return DAL.Supervise.GetSuperviseCompanySC_Chart();
        }

        /// <summary>
        /// 修复所有月份
        /// </summary>
        /// <param name="dt">要不齐的table </param>
        /// <param name="flags">0 季月份 ，1 年月份</param>
        /// <param name="monthcolunmname">月份字段名称</param>
        /// <returns></returns>
        public DataTable FixAllMonth(DataTable dt, int flags, int statioryear, string monthcolunmname)
        {
            return DAL.Supervise.FixAllMonth(dt, flags, statioryear, monthcolunmname);
        }

        /// <summary>
        /// 修复所有月份(有时间选择)
        /// </summary>
        /// <param name="dt">要补齐的table</param>
        /// <param name="Timeto">截至时间</param>
        /// <param name="Timefrom">开始时间</param>
        /// <param name="monthcolunmname">月份字段名称</param>
        /// <returns></returns>
        public DataTable FixAllMonth(DataTable dt, DateTime Timeto, DateTime Timefrom, string monthcolunmname)
        {
            return DAL.Supervise.FixAllMonth(dt, Timeto, Timefrom, monthcolunmname);
        }

        public DataTable GetHospitalCover(string whereAreaid, string starttime, string endtime, int pageindex, int pagesize, out int recordcount)
        {
            string sql = @"SELECT  CompanyID_PS, HospitalCount, CompanyName,
                                    ROW_NUMBER() OVER ( ORDER BY HospitalCount DESC ) Pos
                            FROM    ( SELECT    CompanyID_PS, COUNT(DISTINCT o.HospitalID) HospitalCount
                                      FROM      TB_PurchaseOrderDetail tpod
                                                INNER JOIN TB_PurchaseOrder o ON o.OrderID = tpod.OrderID 
                            WHERE OrderDetailState=2 AND StartTime > '{0}' AND StartTime < '{1}' {2}
                                      GROUP BY  CompanyID_PS ) A
                                    INNER JOIN TB_Company c ON A.CompanyID_PS = c.CompanyID
                            ";

            string sql2 = @"select count(A.HospitalID) from (select distinct A.HospitalID as HospitalID from TB_PurchaseOrder A inner join TB_PurchaseOrderDetail B on A.OrderID = B.OrderID where OrderDetailState=2 AND  StartTime > '{0}' AND StartTime < '{1}' {2}) A ";

            var dt = HZ.Data.DbHelperFactory.Create().ExecuteTable(string.Format(sql, starttime, endtime, whereAreaid));

            recordcount = dt.Rows.Count;

            int allordercount = Convert.ToInt32(HZ.Data.DbHelperFactory.Create().ExecuteScalar(string.Format(sql2, starttime, endtime, whereAreaid)));

            dt.Columns.Add("Rate", typeof(double));
            foreach (DataRow item in dt.Rows)
            {

                item["Rate"] = Convert.ToDouble(item["HospitalCount"]) / allordercount;
            }
            return dt;

        }

        public DataTable GetOrderCover(string whereAreaid, string starttime, string endtime, int pageindex, int pagesize, out int recordcount)
        {
            string sql = @"SELECT  CompanyID_PS, OrderCount, CompanyName,
                                    ROW_NUMBER() OVER ( ORDER BY OrderCount DESC ) Pos
                            FROM    ( SELECT    CompanyID_PS, COUNT(DISTINCT tpod.OrderID) OrderCount
                                      FROM      TB_PurchaseOrderDetail tpod
                                                INNER JOIN TB_PurchaseOrder o ON o.OrderID = tpod.OrderID
                            WHERE OrderDetailState=2 AND StartTime > '{0}' AND StartTime < '{1}' {2}
                                      GROUP BY  CompanyID_PS ) A
                                    INNER JOIN TB_Company c ON A.CompanyID_PS = c.CompanyID
                            ";

            string sql2 = @"select COUNT(*) from (select distinct A.OrderID from TB_PurchaseOrder A inner join TB_PurchaseOrderDetail B on A.OrderID = B.OrderID WHERE OrderDetailState=2 AND StartTime > '{0}' AND StartTime < '{1}' {2}) A ";

            var dt = HZ.Data.DbHelperFactory.Create().ExecuteTable(string.Format(sql, starttime, endtime, whereAreaid));

            recordcount = dt.Rows.Count;


            int allordercount = Convert.ToInt32(HZ.Data.DbHelperFactory.Create().ExecuteScalar(string.Format(sql2, starttime, endtime, whereAreaid)));

            dt.Columns.Add("Rate", typeof(double));

            foreach (DataRow item in dt.Rows)
            {
                item["Rate"] = Convert.ToDouble(item["OrderCount"]) / allordercount;
            }

            return dt;
        }

        public DataTable ExecutePagePSLQuery(string companyname, string orderby, int currentpage, int pagesize, out int totalrecord)
        {
            string where = "";

            if (!string.IsNullOrEmpty(companyname))
            {
                where += "and CompanyName_ps like '%" + companyname + "%'";
            }

            string sql = @"select * from TB_CompanyPSL_PS where 1=1 " + where + " order by " + orderby;

            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);

            des.CurrentPage = currentpage;

            totalrecord = des.TotalCount;

            return des.GetSpecailPage();
        }
        /// <summary>
        /// 生产企业汇总统计
        /// by张雄 2011.5.26
        /// </summary>
        /// <param name="companyname">企业名称</param>
        /// <param name="source">属性</param>
        /// <param name="orderby">排序字段</param>
        /// <param name="currentpage">当前页</param>
        /// <param name="pagesize">每页的行数</param>
        /// <param name="totalrecord">总记录</param>
        /// <returns>生产企业汇总统计数据集</returns>
        public DataTable ExecutePagePSL_SC(int i, string areaid, string companyname, string source, string orderby, int currentpage, int pagesize, out int totalrecord)
        {
            return HZ.Supervise.DAL.Supervise.ExecutePagePSL_SC(i, areaid, companyname, source, orderby, currentpage, pagesize, out totalrecord);
        }

        /// <summary>
        /// 医院汇总统计
        /// </summary>
        /// <param name="hosptialname"></param>
        /// <param name="source"></param>
        /// <param name="orderby"></param>
        /// <param name="currentpage"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalrecord"></param>
        /// <returns></returns>
        public DataTable ExecutePagePSL_HOS(string areaid, string hosptialname, string source, string orderby, int currentpage, int pagesize, out int totalrecord)
        {
            return HZ.Supervise.DAL.Supervise.ExecutePagePSL_HOS(areaid, hosptialname, source, orderby, currentpage, pagesize, out totalrecord);
        }
        /// <summary>
        /// 采购类型汇总
        /// </summary>
        /// <param name="currentpage"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalrecord"></param>
        /// <returns></returns>
        public DataTable ExecutePagePSL_SortSource(string where, string orderby, int currentpage, int pagesize, out int totalrecord)
        {
            return HZ.Supervise.DAL.Supervise.ExecutePagePSL_SortSource(where, orderby, currentpage, pagesize, out totalrecord);
        }

        /// <summary>
        /// 曹鹏
        /// 2011年5月30日17:28:48
        /// 采购类型汇总（根据地区分组）
        /// </summary>
        /// <param name="currentpage"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalrecord"></param>
        /// <returns></returns>
        public DataTable ExecutePagePSL_SortSourceByArea(string str, string where, string orderby, int currentpage, int pagesize, out int totalrecord)
        {
            return HZ.Supervise.DAL.Supervise.ExecutePagePSL_SortSourceByArea(str, where, orderby, currentpage, pagesize, out totalrecord);
        }

        /// <summary>
        /// 药品类别汇总
        /// </summary>
        /// <param name="sortname"></param>
        /// <param name="orderby"></param>
        /// <param name="currentpage"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalrecord"></param>
        /// <returns></returns>
        public DataTable ExecutePagePSL_Sort(int i, string areaid, string sortname, string orderby, int currentpage, int pagesize, out int totalrecord)
        {
            return HZ.Supervise.DAL.Supervise.ExecutePagePSL_Sort(i, areaid, sortname, orderby, currentpage, pagesize, out totalrecord);
        }



        public DataTable getAreaSHIlISTByArea(string orderby)
        {
            return HZ.Supervise.DAL.Supervise.getAreaSHIlISTByArea(orderby);
        }

        public DataTable getAreaXianList(string areaid, string orderby, int currentpage, int pagesize, out int totalrecord)
        {
            return HZ.Supervise.DAL.Supervise.getAreaXianList(areaid, orderby, currentpage, pagesize, out totalrecord);
        }
        /// <summary>
        /// 配送企业列表（高值）
        /// </summary> 
        public DataTable GetCompanyPS_ZongList(HZ.Supervise.Model.View.HospitalProcureCatalog hosproce, int currentpage, int pagesize, out int totalrecord)
        {
            return HZ.Supervise.DAL.Supervise.GetCompanyPS_ZongList(hosproce, currentpage, pagesize, out totalrecord);
        }
        /// <summary>
        /// 配送企业列表（低值）
        /// </summary> 
        public DataTable GetCompanyPS_ZongList_DZ(HZ.Supervise.Model.View.HospitalProcureCatalog hosproce, int currentpage, int pagesize, out int totalrecord)
        {
            return HZ.Supervise.DAL.Supervise.GetCompanyPS_ZongList_DZ(hosproce, currentpage, pagesize, out totalrecord); 
        }
        public DataTable GetCompanyGoodsList(HZ.Supervise.Model.ProcureCatalog proce, int currentpage, int pagesize, out int totalrecord)
        {
            return HZ.Supervise.DAL.Supervise.GetCompanyGoodsList(proce, currentpage, pagesize, out totalrecord);
        }
        /// <summary>
        /// 产品信息列表（高值）
        /// </summary>
        public DataTable GetCompanyGoodsListByAreaID(string areaid, HZ.Supervise.Model.ProcureCatalog proce, int currentpage, int pagesize, out int totalrecord)
        {
            return HZ.Supervise.DAL.Supervise.GetCompanyGoodsListByAreaID(areaid,proce, currentpage, pagesize, out totalrecord);
        }
        /// <summary>
        /// 产品信息列表（低值） 
        /// </summary> 
        public DataTable GetCompanyGoodsListByAreaID_DZ(string areaid, HZ.Supervise.Model.ProcureCatalog proce, int currentpage, int pagesize, out int totalrecord)
        {
            return HZ.Supervise.DAL.Supervise.GetCompanyGoodsListByAreaID_DZ(areaid, proce, currentpage, pagesize, out totalrecord);
        }
        /// <summary>
        /// 查询医院详细（高值）
        /// </summary> 
        public DataTable GetCompanyGoodsList_ps(HZ.Supervise.Model.View.HospitalProcureCatalog proce, int currentpage, int pagesize, out int totalrecord)
        {
            return HZ.Supervise.DAL.Supervise.GetCompanyGoodsList_ps(proce, currentpage, pagesize, out totalrecord);
        }
        /// <summary> 
        /// 查询医院详细（低值）
        /// </summary> 
        public DataTable GetCompanyGoodsList_ps_DZ(HZ.Supervise.Model.View.HospitalProcureCatalog proce, int currentpage, int pagesize, out int totalrecord)
        {
            return HZ.Supervise.DAL.Supervise.GetCompanyGoodsList_ps_DZ(proce, currentpage, pagesize, out totalrecord);
        }
        /// <summary>
        /// 查看医院列表（高值）
        /// </summary>  
        public DataTable GetHospitalInfo(HZ.Supervise.Model.View.HospitalProcureCatalog hosproce, int currentpage, int pagesize, out int totalrecord)
        {
            return HZ.Supervise.DAL.Supervise.GetHospitalInfo(hosproce, currentpage, pagesize, out totalrecord);
        }
        /// <summary>
        /// 查看医院列表（低值） 
        /// </summary>  
        public DataTable GetHospitalInfo_DZ(HZ.Supervise.Model.View.HospitalProcureCatalog hosproce, int currentpage, int pagesize, out int totalrecord)
        {
            return HZ.Supervise.DAL.Supervise.GetHospitalInfo_DZ(hosproce, currentpage, pagesize, out totalrecord); 
        }
        /// <summary>
        /// 产品明细信息（高值）
        /// </summary> 
        public DataTable GetCompanyPsList_JG_Message(HZ.Supervise.Model.View.HospitalProcureCatalog hosproce, int currentpage, int pagesize, out int totalrecord)
        {
            return HZ.Supervise.DAL.Supervise.GetCompanyPsList_JG_Message(hosproce, currentpage, pagesize, out totalrecord);
        }
        /// <summary>
        /// 产品明细信息（低值） 
        /// </summary> 
        public DataTable GetCompanyPsList_JG_Message_DZ(HZ.Supervise.Model.View.HospitalProcureCatalog hosproce, int currentpage, int pagesize, out int totalrecord)
        {
            return HZ.Supervise.DAL.Supervise.GetCompanyPsList_JG_Message_DZ(hosproce, currentpage, pagesize, out totalrecord);
        }
        /// <summary>
        /// 产品相关配送企业和医院列表（高值）
        /// </summary> 
        public DataTable GetCompanyPsList_JG(HZ.Supervise.Model.View.HospitalProcureCatalog hosproce, int currentpage, int pagesize, out int totalrecord)
        {
            return HZ.Supervise.DAL.Supervise.GetCompanyPsList_JG(hosproce, currentpage, pagesize, out totalrecord);
        }
        /// <summary>
        /// 产品相关配送企业和医院列表（低值）
        /// </summary> 
        public DataTable GetCompanyPsList_JG_DZ(HZ.Supervise.Model.View.HospitalProcureCatalog hosproce, int currentpage, int pagesize, out int totalrecord)
        {
            return HZ.Supervise.DAL.Supervise.GetCompanyPsList_JG_DZ(hosproce, currentpage, pagesize, out totalrecord); 
        }
        /// <summary>
        /// 配送企业配送率静态报表统计
        /// </summary>
        /// <param name="areaid"></param>
        /// <param name="companyname"></param>
        /// <param name="sortsource"></param>
        /// <param name="orderby"></param>
        /// <param name="currentpage"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalrecord"></param>
        /// <returns></returns>
        public DataTable ExecutePagePSLOne(SuperviseQuery vs, int currentpage, int pagesize, out int totalrecord)
        {

            return HZ.Supervise.DAL.Supervise.ExecutePagePSLOne(vs, currentpage, pagesize, out totalrecord);
        }
        #region IExecuteContext 成员

        /// <summary>
        /// 曹鹏
        /// 2011年5月12日13:47:41
        /// 医院月报
        /// </summary>
        /// <param name="where"></param>
        /// <returns></returns>
        /// 
        public DataTable StatisticsHospitalByMonth(string hid, string year, string month)
        {

            StringBuilder sb = new StringBuilder();

            if (!string.IsNullOrEmpty(hid))
            {

                sb.AppendFormat(" and HospitalID = '{0}'", hid);

            }

            if (!(string.IsNullOrEmpty(year) && string.IsNullOrEmpty(month)))
            {

                sb.AppendFormat(" and daytime = {0}", year + month);

            }

            return HZ.Supervise.DAL.Supervise.StatisticsHospitalByMonth(sb.ToString());

        }
        /// <summary>
        /// 曹鹏
        /// 2011年5月12日16:19:34
        /// 医院季报
        /// </summary>
        /// <param name="hid"></param>
        /// <param name="year"></param>
        /// <param name="Season"></param>
        /// <returns></returns>
        public DataTable StatisticsHospitalBySeason(string hid, string year, string Season)
        {

            StringBuilder sb = new StringBuilder();

            if (!string.IsNullOrEmpty(hid))
            {

                sb.AppendFormat(" and HospitalName = '{0}'", hid);

            }

            if (!(string.IsNullOrEmpty(year) && string.IsNullOrEmpty(Season)))
            {

                switch (Season)
                {
                    case "1"://当第一个季度

                        sb.AppendFormat(" and daytime in ({0}01,{0}02,{0}03)", year);

                        break;

                    case "2"://当第二个季度

                        sb.AppendFormat(" and daytime in ({0}04,{0}05,{0}06)", year);

                        break;

                    case "3"://当第三个季度

                        sb.AppendFormat(" and daytime in ({0}07,{0}08,{0}09)", year);

                        break;

                    case "4"://当第四个季度

                        sb.AppendFormat(" and daytime in ({0}10,{0}11,{0}12)", year);

                        break;

                }
            }

            return HZ.Supervise.DAL.Supervise.StatisticsHospitalBySeason(sb.ToString());

        }

        /// <summary>
        /// 医院季报饼状
        /// by张雄  
        /// </summary>
        /// <param name="hid"></param>
        /// <param name="year"></param>
        /// <param name="Season"></param>
        /// <returns></returns>
        public DataTable StatisticsHospitalBySeasonPie(string hid, string year, string Season)
        {
            StringBuilder sb = new StringBuilder();

            if (!string.IsNullOrEmpty(hid))
            {

                sb.AppendFormat(" and HospitalName = '{0}'", hid);

            }

            if (!(string.IsNullOrEmpty(year) && string.IsNullOrEmpty(Season)))
            {

                switch (Season)
                {
                    case "1"://当第一个季度

                        sb.AppendFormat(" and daytime in ({0}01,{0}02,{0}03)", year);

                        break;

                    case "2"://当第二个季度

                        sb.AppendFormat(" and daytime in ({0}04,{0}05,{0}06)", year);

                        break;

                    case "3"://当第三个季度

                        sb.AppendFormat(" and daytime in ({0}07,{0}08,{0}09)", year);

                        break;

                    case "4"://当第四个季度

                        sb.AppendFormat(" and daytime in ({0}10,{0}11,{0}12)", year);

                        break;

                }
            }
            return HZ.Supervise.DAL.Supervise.StatisticsHospitalBySeasonPie(sb.ToString());
        }

        /// <summary>
        /// 曹鹏   
        /// 2011年5月12日16:19:54
        /// 医院年报
        /// </summary>
        /// <param name="hid"></param>
        /// <param name="year"></param>
        /// <returns></returns>
        public DataTable StatisticsHospitalByYear(string hid, string timeFrom, string timeTo)
        {

            StringBuilder sb = new StringBuilder();
            if (!string.IsNullOrEmpty(hid.Trim()))
            {

                sb.AppendFormat(" and HospitalName = '{0}'", hid.Trim());//拼接查询条件
            }


            if (!string.IsNullOrEmpty(timeFrom.Trim()))
            {
                sb.AppendFormat(" and daytime >= {0} ", timeFrom.Trim().Remove(4, 1));

            }

            if (!string.IsNullOrEmpty(timeTo.Trim()))
            {
                sb.AppendFormat(" and daytime <= {0}", timeTo.Trim().Remove(4, 1));

            }

            return HZ.Supervise.DAL.Supervise.StatisticsHospitalByYear(sb.ToString());

        }

        /// <summary>
        /// 医院年报    报表
        /// by张雄  2011.5.24
        /// </summary>
        /// <param name="hid"></param>
        /// <param name="timeFrom"></param>
        /// <param name="timeTo"></param>
        /// <returns></returns>
        public DataTable StatisticsHospitalByYearNew(string hname, string timeFrom, string timeTo)
        {

            StringBuilder sb = new StringBuilder();

            if (!string.IsNullOrEmpty(hname.Trim()))
            {

                sb.AppendFormat(" and HospitalName = '{0}'", hname.Trim());//拼接查询条件

            }
            if (!string.IsNullOrEmpty(timeFrom.Trim()))
            {
                sb.AppendFormat(" and daytime >= {0} ", timeFrom.Trim().Remove(4, 1));

            }

            if (!string.IsNullOrEmpty(timeTo.Trim()))
            {
                sb.AppendFormat(" and daytime <= {0}", timeTo.Trim().Remove(4, 1));

            }
            return HZ.Supervise.DAL.Supervise.StatisticsHospitalByYearNew(sb.ToString());
        }

        #endregion


        #region IExecuteContext 成员

        /// <summary>
        /// 曹鹏   
        /// 2011年5月18日11:18:43
        /// 医院年报（饼状图）
        /// </summary>
        /// <param name="hid"></param>
        /// <param name="year"></param>
        /// <returns></returns>
        public DataTable StatisticsHospitalByYearPie(string hid, string timeFrom, string timeTo)
        {

            StringBuilder sb = new StringBuilder();

            sb.AppendFormat(" and HospitalName = '{0}'", hid);//拼接查询条件

            if (!string.IsNullOrEmpty(timeFrom.Trim()))
            {
                sb.AppendFormat(" and daytime >= {0} ", timeFrom.Trim().Remove(4, 1));

            }

            if (!string.IsNullOrEmpty(timeTo.Trim()))
            {
                sb.AppendFormat(" and daytime <= {0}", timeTo.Trim().Remove(4, 1));

            }
            return HZ.Supervise.DAL.Supervise.StatisticsHospitalByYearPie(sb.ToString());
        }

        #endregion



        #region IExecuteContext 成员

        /// <summary>
        /// 曹鹏
        /// 2011年5月20日9:23:29
        /// 医院某采购类型所在比例
        /// </summary>
        /// <param name="hosName"></param>
        /// <param name="sortSource"></param>
        /// <param name="rate"></param>
        /// <param name="compare"></param>
        /// <param name="areaid"></param>
        /// <param name="pageSize"></param>
        /// <param name="currentPageIndex"></param>
        /// <param name="total"></param>
        /// <returns></returns>
        public DataTable ExecuteRateOfHospitalSource(string timeFrom, string timeTo, string hosName, string sortSource, string whereareaid, string compare, string rate, string sort, int pageSize, int currentPageIndex, out int total)
        {
            StringBuilder sb = new StringBuilder();
            StringBuilder sc = new StringBuilder();


            //xg by张雄

            string strSortSource = "";  //加一个采购类别变量

            if (!string.IsNullOrEmpty(timeFrom.Trim()))
            {
                sb.AppendFormat("and DayTime >='{0}'", timeFrom.Trim().Remove(4, 1));
            }

            if (!string.IsNullOrEmpty(timeTo.Trim()))
            {
                sb.AppendFormat("and DayTime <='{0}'", timeTo.Trim().Remove(4, 1));
            }

            if (!string.IsNullOrEmpty(hosName.Trim()))
            {

                sb.AppendFormat(" and HospitalName like '%{0}%'", hosName.Trim());

            }

            //if (!string.IsNullOrEmpty(sortSource.Trim()) && (sortSource.Trim() != "''"))
            //{

            //    sb.AppendFormat(" and SortSorce = {0}", sortSource);

            //}

            if (!string.IsNullOrEmpty(sortSource.Trim()) && (sortSource.Trim()) != "''")
            {
                strSortSource += "and SortSorce = '" + sortSource + "'";
            }

            if (!string.IsNullOrEmpty(compare) && (!string.IsNullOrEmpty(rate.Trim())))
            {
                if (compare == "greater")
                {

                    sc.AppendFormat(" and sortsourcerate >= {0}", rate);

                }
                else
                {

                    sc.AppendFormat(" and sortsourcerate<= {0}", rate);

                }
            }

            return HZ.Supervise.DAL.Supervise.ExecuteRateOfHospitalSource(sb.ToString(), sc.ToString(), whereareaid, sort, strSortSource, pageSize, currentPageIndex, out total);

        }

        #endregion


        /// <summary>
        /// 采购次数
        /// by：张雄
        /// </summary>
        /// <param name="timeFrom">时间从</param>
        /// <param name="timeTo">到</param>
        /// <param name="hosName">医院名称</param>
        /// <param name="areaid">地区Id</param>
        /// <returns></returns>
        public DataTable ExecuteRateOfHosSourceCount(string timeFrom, string timeTo, string hosName, string areaid, string flag, int pageSize, int currentPageIndex, out int t)
        {
            StringBuilder sb = new StringBuilder();

            if (!string.IsNullOrEmpty(timeFrom.Trim()))
            {
                sb.AppendFormat("and PlanTime >='{0}'", timeFrom.Trim());
            }

            if (!string.IsNullOrEmpty(timeTo.Trim()))
            {
                sb.AppendFormat("and PlanTime <='{0}'", timeTo.Trim());
            }

            if (!string.IsNullOrEmpty(hosName.Trim()))
            {
                sb.AppendFormat(" and HospitalName like '%{0}%'", hosName.Trim());
            }

            return HZ.Supervise.DAL.Supervise.ExecuteRateOfHosSourceCount(sb.ToString(), areaid, flag, pageSize, currentPageIndex, out t);

        }


        #region IExecuteContext 成员


        public DataTable ExecuteRateOfAreaSource(string timeFrom, string timeTo, string sortSource, string whereareaid, string compare, string rate, string sort, int pageSize, int currentPageIndex, out int total)
        {
            //xg by张雄
            StringBuilder sb = new StringBuilder();
            StringBuilder sc = new StringBuilder();
            string strSortSource = "";
            if (!string.IsNullOrEmpty(timeFrom.Trim()))
            {
                sb.AppendFormat("and DayTime >='{0}'", timeFrom.Trim().Remove(4, 1));
            }

            if (!string.IsNullOrEmpty(timeTo.Trim()))
            {
                sb.AppendFormat("and DayTime <='{0}'", timeTo.Trim().Remove(4, 1));
            }

            //if (!string.IsNullOrEmpty(sortSource.Trim()) && (sortSource.Trim() != "''"))
            //{

            //    sb.AppendFormat(" and SortSorce = {0}", sortSource);

            //}

            if (!string.IsNullOrEmpty(sortSource.Trim()) && (sortSource.Trim()) != "''")
            {
                strSortSource += "and SortSorce = '" + sortSource + "'";
            }

            if (!string.IsNullOrEmpty(compare) && (!string.IsNullOrEmpty(rate.Trim())))
            {
                if (compare == "greater")
                {

                    sc.AppendFormat(" and sortsourcerate >= {0}", rate);

                }
                else
                {

                    sc.AppendFormat(" and sortsourcerate<= {0}", rate);

                }
            }

            return HZ.Supervise.DAL.Supervise.ExecuteRateOfHAreaSource(sb.ToString(), sc.ToString(), whereareaid, sort, strSortSource, pageSize, currentPageIndex, out total);
        }

        #endregion
        /// <summary>
        /// 曹鹏
        /// 2011年6月1日9:23:39
        /// 地区采购类型所占比例（城市）
        /// </summary>
        /// <param name="areaGroupBy"></param>
        /// <param name="sortSource"></param>
        /// <param name="whereareaid"></param>
        /// <param name="compare"></param>
        /// <param name="rate"></param>
        /// <param name="sort"></param>
        /// <param name="pageSize"></param>
        /// <param name="currentPageIndex"></param>
        /// <param name="total"></param>
        /// <returns></returns>
        public DataTable ExecuteRateOfAreaSource1(string timeFrom, string timeTo, string City, string areaGroupBy, string sortSource, string compare, string rate, string sort, int pageSize, int currentPageIndex, out int total)
        {
            StringBuilder sb = new StringBuilder();
            StringBuilder sc = new StringBuilder();
            //xg by 张雄

            string strSortSource = "";

            if (!string.IsNullOrEmpty(timeFrom.Trim()))
            {
                sb.AppendFormat("and DayTime >='{0}'", timeFrom.Trim().Remove(4, 1));
            }

            if (!string.IsNullOrEmpty(timeTo.Trim()))
            {
                sb.AppendFormat("and DayTime <='{0}'", timeTo.Trim().Remove(4, 1));
            }


            if (!string.IsNullOrEmpty(sortSource.Trim()) && (sortSource.Trim() != "''"))
            {
                strSortSource = "and SortSorce = '" + sortSource + "'";
            }

            if (!string.IsNullOrEmpty(City) && City != "510000")
            {
                sc.AppendFormat("and C.AreaID='{0}'", City);
            }
            if (!string.IsNullOrEmpty(compare) && (!string.IsNullOrEmpty(rate.Trim())))
            {
                if (compare == "greater")
                {

                    sc.AppendFormat(" and sortsourcerate >= {0}", rate);

                }
                else
                {

                    sc.AppendFormat(" and sortsourcerate<= {0}", rate);

                }
            }

            return HZ.Supervise.DAL.Supervise.ExecuteRateOfHAreaSource1(timeFrom, timeTo, areaGroupBy, sb.ToString(), sc.ToString(), sort, strSortSource, pageSize, currentPageIndex, out total);
        }

        public DataTable ExecutePagePSL_SC(HZ.Supervise.Model.SuperviseQuery vs, int currentpage, int pagesize, out int totalrecord)
        {
            return HZ.Supervise.DAL.Supervise.ExecutePagePSL_SC(vs, currentpage, pagesize, out totalrecord);

        }

        /// <summary>
        /// 订单明细查看（高值）
        /// </summary> 
        public DataTable GoodsOrderDetail(string where, int i, int currentpage, int pagesize, out int totalrecord)
        {
            return HZ.Supervise.DAL.Supervise.GoodsOrderDetail(where, i, currentpage, pagesize, out totalrecord);
        }
        /// <summary>
        /// 订单明细查看（低值）
        /// </summary> 
        public DataTable GoodsOrderDetail_DZ(string where, int i, int currentpage, int pagesize, out int totalrecord)
        {
            return HZ.Supervise.DAL.Supervise.GoodsOrderDetail_DZ(where, i, currentpage, pagesize, out totalrecord); 
        }
        /// <summary>
        /// 生产企业分类汇总
        /// </summary>
        /// <param name="where"></param>
        /// <param name="i"></param>
        /// <param name="currentpage"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalrecord"></param>
        /// <returns></returns>
        public DataTable CompanySC_InfoByType(SuperviseQuery vs, int currentpage, int pagesize, out int totalrecord)
        {
            return HZ.Supervise.DAL.Supervise.CompanySC_InfoByType(vs, currentpage, pagesize, out totalrecord);

        }
        /// <summary>
        /// 配送企业分类汇总
        /// </summary>
        /// <param name="where"></param>
        /// <param name="i"></param>
        /// <param name="currentpage"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalrecord"></param>
        /// <returns></returns>
        public DataTable CompanyPS_InfoByType(SuperviseQuery vs, int currentpage, int pagesize, out int totalrecord)
        {
            return HZ.Supervise.DAL.Supervise.CompanyPS_InfoByType(vs, currentpage, pagesize, out totalrecord);

        }

        /// <summary>
        /// 医院统计
        /// </summary>
        /// <param name="i">1代表医院统计，2代表医院下面药品统计</param>
        /// <param name="IsCountry">医疗机构</param>
        /// <param name="timeto">起始时间</param>
        /// <param name="timeform">结束时间</param>
        /// <param name="strwhere">where条件</param>
        /// <param name="orderby">排序方式</param>
        /// <param name="currentpage"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalrecord"></param>
        /// <returns></returns>
        public DataTable GetHospitalStatistics(int i, string IsCountry, string timeto, string timeform, string strwhere, string orderby, string areaid, int currentpage, int pagesize, out int totalrecord)
        {
            return HZ.Supervise.DAL.Supervise.GetHospitalStatistics(i, IsCountry, timeto, timeform, strwhere, orderby, areaid, currentpage, pagesize, out totalrecord);
        }

        /// <summary>
        /// 药品汇总统计（高值）
        /// </summary>
        /// <param name="i">哪个数据源</param>
        /// <param name="strwhere">where条件</param> 
        public DataTable GetGoodsStatistics(int i, string strwhere, int currentpage, int pagesize, out int totalrecord)
        {
            return HZ.Supervise.DAL.Supervise.GetGoodsStatistics(i, strwhere, currentpage, pagesize, out totalrecord);
        }
        /// <summary>
        /// 药品汇总统计（低值）
        /// </summary>
        /// <param name="i">哪个数据源</param>
        /// <param name="strwhere">where条件</param> 
        public DataTable GetGoodsStatistics_DZ(int i, string strwhere, int currentpage, int pagesize, out int totalrecord)
        {
            return HZ.Supervise.DAL.Supervise.GetGoodsStatistics_DZ(i, strwhere, currentpage, pagesize, out totalrecord);
        }
        /// <summary>
        /// 月报统计1（高值）
        /// </summary>
        /// <param name="flag"></param> 
        public DataTable GetReport1(string orderby, HZ.Supervise.Model.SuperviseQuery vs, int currentpage, int pagesize, out int totalrecord)
        {
            return HZ.Supervise.DAL.Supervise.GetReport1(orderby, vs, currentpage, pagesize, out  totalrecord);
        }
        /// <summary>
        /// 月报统计2（高值）
        /// </summary>
        /// <param name="flag"></param> 
        public DataTable GetReport2(HZ.Supervise.Model.SuperviseQuery vs, int currentpage, int pagesize, out int totalrecord)
        {
            return HZ.Supervise.DAL.Supervise.GetReport2(vs, currentpage, pagesize, out  totalrecord);
        }
        /// <summary>
        /// 月报统计3（高值）
        /// </summary>
        /// <param name="flag"></param> 
        public DataTable GetReport3(HZ.Supervise.Model.SuperviseQuery vs, int currentpage, int pagesize, out int totalrecord)
        {
            return HZ.Supervise.DAL.Supervise.GetReport3(vs, currentpage, pagesize, out  totalrecord);
        }
        /// <summary>
        /// 月报统计4（高值）
        /// </summary>
        /// <param name="flag"></param> 
        public DataTable GetReport4(HZ.Supervise.Model.SuperviseQuery vs, int currentpage, int pagesize, out int totalrecord)
        {
            return HZ.Supervise.DAL.Supervise.GetReport4(vs, currentpage, pagesize, out  totalrecord);
        }
        /// <summary>
        /// 月报统计5（高值）
        /// </summary>
        /// <param name="flag"></param> 
        public DataTable GetReport5(DateTime SubmiTimeFrom, DateTime SubmiTimeTo, HZ.Supervise.Model.SuperviseQuery vs, int currentpage, int pagesize, out int totalrecord)
        {
            return HZ.Supervise.DAL.Supervise.GetReport5(SubmiTimeFrom, SubmiTimeTo,vs, currentpage, pagesize, out  totalrecord);
        }
        /// <summary>
        /// 月报统计6（高值）
        /// </summary>
        /// <param name="flag"></param> 
        public DataTable GetReport6(string orderby, HZ.Supervise.Model.SuperviseQuery vs, int currentpage, int pagesize, out int totalrecord)
        {
            return HZ.Supervise.DAL.Supervise.GetReport6(orderby, vs, currentpage, pagesize, out  totalrecord);
        }
        /// <summary>
        /// 月报统计7（高值）
        /// </summary>
        /// <param name="flag"></param> 
        public DataTable GetReport7(HZ.Supervise.Model.SuperviseQuery vs, int currentpage, int pagesize, out int totalrecord)
        {
            return HZ.Supervise.DAL.Supervise.GetReport7(vs, currentpage, pagesize, out  totalrecord);
        }
        /// <summary>
        /// 月报统计8（高值）
        /// </summary>
        /// <param name="flag"></param> 
        public DataTable GetReport8(HZ.Supervise.Model.SuperviseQuery vs, int currentpage, int pagesize, out int totalrecord)
        {
            return HZ.Supervise.DAL.Supervise.GetReport8(vs, currentpage, pagesize, out  totalrecord);
        }
        /// <summary>
        /// 最高价及最低价采购情况
        /// </summary>
        /// <param name="flag"></param> 
        public DataTable GetReportHL(string str, int currentPage, int pageSize, out int totalRecord)
        {
            return HZ.Supervise.DAL.Supervise.GetReportHL(str, currentPage, pageSize, out totalRecord);
        }
        /// <summary>
        /// 最高价采购明细
        /// </summary>
        /// <param name="flag"></param> 
        public DataTable GetReportH(string str, int currentPage, int pageSize, out int totalRecord)
        {
            return HZ.Supervise.DAL.Supervise.GetReportH(str, currentPage, pageSize, out totalRecord);
        }
        /// <summary>
        /// 最低价采购明细
        /// </summary>
        /// <param name="flag"></param> 
        public DataTable GetReportL(string str, int currentPage, int pageSize, out int totalRecord)
        {
            return HZ.Supervise.DAL.Supervise.GetReportL(str, currentPage, pageSize, out totalRecord);
        }
        /// <summary>
        /// 统计数据（高值）
        /// </summary>
        /// <param name="flag">1、医院统计 2、配送企业统计</param> 
        public DataTable GetStatistics(int flag, string orderby, HZ.Supervise.Model.SuperviseQuery vs, int currentpage, int pagesize, out int totalrecord)
        {
            return HZ.Supervise.DAL.Supervise.GetStatistics(flag, orderby, vs, currentpage, pagesize, out  totalrecord);
        }
        /// <summary>
        /// 统计数据（低值）
        /// </summary>
        /// <param name="flag">1、医院统计 2、配送企业统计</param> 
        public DataTable GetStatistics_DZ(int flag, string orderby, HZ.Supervise.Model.SuperviseQuery vs, int currentpage, int pagesize, out int totalrecord)
        { 
            return HZ.Supervise.DAL.Supervise.GetStatistics_DZ(flag, orderby, vs, currentpage, pagesize, out  totalrecord);
        }
        /// <summary>
        /// 查看医院订单明细
        /// </summary>
        public DataTable GetHospitalStatisticsMX(string orderby, string where, int currentPage, int pageSize, out int totalRecord)
        {
            return HZ.Supervise.DAL.Supervise.GetHospitalStatisticsMX(orderby, where, currentPage, pageSize, out totalRecord);
        }
        /// <summary>
        /// 统计医院订单明细
        /// </summary>
        public DataTable GetHospitalStatisticsCount(string where)
        {
            return HZ.Supervise.DAL.Supervise.GetHospitalStatisticsCount(where);
        }
        /// <summary>
        /// 查看医疗机构大型设备采购合同
        /// </summary> 
        public DataTable GetOrderDXSB(HZ.Supervise.Model.Query.QOrderDXSB query, int currentPage, int pageSize, out int totalRecord)
        {
            return HZ.Supervise.DAL.Supervise.GetOrderDXSB(query, currentPage, pageSize, out  totalRecord);
        }
        public DataTable GetOrderDXSB2(string str, int currentPage, int pageSize, out int totalRecord)
        {
            return HZ.Supervise.DAL.Supervise.GetOrderDXSB2(str, currentPage, pageSize, out  totalRecord);
        }
        /// <summary>
        /// 获取分类名称
        /// </summary>  
        public DataTable GetSortNameDXSB()
        {
            return HZ.Supervise.DAL.Supervise.GetSortNameDXSB();
        }
        /// <summary>
        /// 医疗机构获取合同内明细
        /// </summary> 
        public DataTable GetOrderDetailDXSB(HZ.Supervise.Model.Query.QGoodsDXSB query, int currentPage, int pageSize, out int totalRecord)
        {
            return HZ.Supervise.DAL.Supervise.GetOrderDetailDXSB(query, currentPage, pageSize, out  totalRecord);
        }
        /// <summary>
        /// 获取单个合同内的明细
        /// </summary>  
        public DataTable GetOneDetailDXSB(string str)
        {
            return HZ.Supervise.DAL.Supervise.GetOneDetailDXSB(str);
        }
        /// <summary>
        /// 中心获取所有明细
        /// </summary> 
        public DataTable GetAllDetailDXSB2(HZ.Supervise.Model.Query.QGoodsDXSB query, int currentPage, int pageSize, out int totalRecord)
        {
            return HZ.Supervise.DAL.Supervise.GetAllDetailDXSB2(query, currentPage, pageSize, out  totalRecord);
        }
        /// <summary>
        /// 获取类别采购信息
        /// </summary>          
         /// <summary>
        public DataTable GetAllPurchaseSummary(HZ.Supervise.Model.Query.QGoodsDXSB query, int currentPage, int pageSize, out int totalRecord)
        {
            return HZ.Supervise.DAL.Supervise.GetAllPurchaseSummary(query, currentPage, pageSize, out  totalRecord);
        }
        /// <summary>
        /// 获取类别采购信息
        /// </summary>          
        /// <summary>
        public DataTable GetAllPurchaseSummaryTotal(HZ.Supervise.Model.Query.QGoodsDXSB query)
        {
            return HZ.Supervise.DAL.Supervise.GetAllPurchaseSummaryTotal(query);
        }
        public DataTable GetAllPurchaseSummaryTotal2(HZ.Supervise.Model.Query.QGoodsDXSB query)
        {
            return HZ.Supervise.DAL.Supervise.GetAllPurchaseSummaryTotal2(query);
        }
        public DataTable GetAllPurchaseSummaryTotal3(HZ.Supervise.Model.Query.QGoodsDXSB query)
        {
            return HZ.Supervise.DAL.Supervise.GetAllPurchaseSummaryTotal3(query);
        }
        public DataTable GetAllPurchaseSummaryTotal4(HZ.Supervise.Model.Query.QGoodsDXSB query)
        {
            return HZ.Supervise.DAL.Supervise.GetAllPurchaseSummaryTotal4(query);
        }
        /// <summary>
        ///  获取裸机价格信息
        /// </summary>          
        /// <summary>
        public DataTable GetBarepriceGT(HZ.Supervise.Model.Query.QGoodsDXSB query, int currentPage, int pageSize, out int totalRecord)
        {
            return HZ.Supervise.DAL.Supervise.GetBarepriceGT(query, currentPage, pageSize, out  totalRecord);
        }
        public DataTable GetBarepriceGTTotal(HZ.Supervise.Model.Query.QGoodsDXSB query)
        {
            return HZ.Supervise.DAL.Supervise.GetBarepriceGTTotal(query);
        }
        /// 获取地区下的医疗机构
        /// </summary>
        /// <param name="vs"></param>
        /// <param name="currentpage"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalrecord"></param>
        /// <returns></returns>
        public DataTable GetHospitalByAreaId(string where, int currentpage, int pagesize, out int totalrecord)
        {
            return HZ.Supervise.DAL.Supervise.GetHospitalByAreaId(where, currentpage, pagesize,out totalrecord);
        }
        /// <summary>
        /// 获取阳光积分
        /// </summary>
        /// <param name="vs"></param>
        /// <param name="currentpage"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalrecord"></param>
        /// <returns></returns>
        public DataTable GetSunScore(string where, int currentpage, int pagesize, out int totalrecord)
        {
            return HZ.Supervise.DAL.Supervise.GetSunScore(where, currentpage, pagesize, out totalrecord);
        }
        public DataTable GetWarnword(string str, int currentPage, int pageSize, out int totalRecord)
        {
            //预警
            return HZ.Supervise.DAL.Supervise.GetWarnword(str, currentPage, pageSize, out totalRecord);
        }
        /// <summary>
        /// 更新提交状态
        /// </summary>
        /// <param name="goodsid"></param>
        /// <returns></returns>
        public int WarnwordsSubmit(string Urlid, int flag)
        {
            return HZ.Supervise.DAL.Supervise.WarnwordsSubmit(Urlid, flag);
        }
         
        /// <summary>
        /// 查看预警月
        /// </summary> 
        public DataTable GetWarnwords(string str, int currentPage, int pageSize, out int totalRecord)
        {

            return HZ.Supervise.DAL.Supervise.GetWarnwords(str, currentPage, pageSize, out totalRecord);
        }
        /// <summary>
        /// 查看预警数据
        /// </summary> 
        public DataTable GetHospitalWarn(string str, int currentPage, int pageSize, out int totalRecord)
        {
            //验证
            return HZ.Supervise.DAL.Supervise.GetHospitalWarn(str, currentPage, pageSize, out totalRecord);
        }
        /// <summary>
        /// 查看医疗机构阳光积分
        /// </summary>
        public DataTable GetSunMark(string str, int currentPage, int pageSize, out int totalRecord)
        {
            return HZ.Supervise.DAL.Supervise.GetSunMark(str, currentPage, pageSize, out totalRecord);
        }
        /// <summary>
        /// 医院积分情况统计
        /// </summary>
        public DataTable GetHospitalScoreStatistics(string str, int currentPage, int pageSize, out int totalRecord)
        {
            return HZ.Supervise.DAL.Supervise.GetHospitalScoreStatistics(str, currentPage, pageSize, out totalRecord);
        }
        /// <summary>
        /// 新增一个阳光积分数据
        /// </summary>
        /// <param name="sun"></param>
        /// <returns></returns>
        public int InsertSunScore(SunScore sun)
        {
            return HZ.Supervise.DAL.Supervise.InsertSunScore(sun);
        }

        /// <summary>
        /// 获取一个医院的积分
        /// </summary>
        /// <param name="hospitalid"></param>
        /// <param name="year"></param>
        /// <param name="month"></param>
        /// <returns></returns>
        public DataTable GetSunScoreByHospital(string hospitalid, int year, int month)
        {
            return HZ.Supervise.DAL.Supervise.GetSunScoreByHospital(hospitalid, year, month);
        }

        /// <summary>
        /// 修改一个阳光积分
        /// </summary>
        /// <param name="sun"></param>
        /// <returns></returns>
        public int UpdateSunScore(SunScore sun)
        {
            return HZ.Supervise.DAL.Supervise.UpdateSunScore(sun);
        }

        /// <summary>
        /// todo：汇总金额类别统计
        /// JOHN
        /// </summary>
        /// <returns>汇总金额类别统计数据源</returns>
        public System.Data.DataTable GetAmountType(string str)
        {
            return HZ.Supervise.DAL.Supervise.GetAmountType(str);
        }

        /// <summary>
        /// todo：汇总金额，按照月份
        /// JOHN
        /// </summary>
        /// <returns></returns>
        public System.Data.DataTable GetAmountByMonth(string str, int currentpage, int pagesize, out int totalrecord)
        {
            return HZ.Supervise.DAL.Supervise.GetAmountByMonth(str, currentpage, pagesize, out totalrecord);
        }
        /// <summary>
        /// 超过12小时未响应的药品
        /// </summary>
        /// <param name="pro"></param>
        /// <returns></returns>
        public DataTable Get12HourByProcureCatalog(SuperviseQuery sq, int currentpage, int pagesize, out int totalrecord)
        {
            return HZ.Supervise.DAL.Supervise.Get12HourByProcureCatalog(sq, currentpage, pagesize, out totalrecord);

        }

        /// <summary>
        /// 超过12小时未配送的企业列表
        /// </summary>
        /// <param name="sq"></param>
        /// <param name="currentpage"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalrecord"></param>
        /// <returns></returns>
        public DataTable Get12HourByCompany(SuperviseQuery sq, int currentpage, int pagesize, out int totalrecord)
        {
            return HZ.Supervise.DAL.Supervise.Get12HourByCompany(sq, currentpage, pagesize, out totalrecord);

        }

        /// <summary>
        /// 超过12小时未配送的采购详情
        /// </summary>
        /// <param name="sq"></param>
        /// <param name="currentpage"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalrecord"></param>
        /// <returns></returns>
        public DataTable Get12HourByPurchase(SuperviseQuery sq, int currentpage, int pagesize, out int totalrecord)
        {
            return HZ.Supervise.DAL.Supervise.Get12HourByPurchase(sq, currentpage, pagesize, out totalrecord);
        }
        /// <summary>
        /// 超过24小时未响应的药品
        /// </summary>
        /// <param name="pro"></param>
        /// <returns></returns>
        public DataTable Get24HourByProcureCatalog(SuperviseQuery sq, int currentpage, int pagesize, out int totalrecord)
        {
            return HZ.Supervise.DAL.Supervise.Get24HourByProcureCatalog(sq, currentpage, pagesize, out totalrecord);

        }

        /// <summary>
        /// 超过24小时未配送的企业列表
        /// </summary>
        /// <param name="sq"></param>
        /// <param name="currentpage"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalrecord"></param>
        /// <returns></returns>
        public DataTable Get24HourByCompany(SuperviseQuery sq, int currentpage, int pagesize, out int totalrecord)
        {
            return HZ.Supervise.DAL.Supervise.Get24HourByCompany(sq, currentpage, pagesize, out totalrecord);
        }

        /// <summary>
        /// 超过24小时未配送的采购详情
        /// </summary>
        /// <param name="sq"></param>
        /// <param name="currentpage"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalrecord"></param>
        /// <returns></returns>
        public DataTable Get24HourByPurchase(SuperviseQuery sq, int currentpage, int pagesize, out int totalrecord)
        {
            return HZ.Supervise.DAL.Supervise.Get24HourByPurchase(sq, currentpage, pagesize, out totalrecord);
        }


        /// <summary>
        /// 获取医疗机构计划采购与实际入库的数据
        /// </summary>
        /// <param name="sq"></param>
        /// <param name="currentpage"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalrecord"></param>
        /// <returns></returns>
        public DataTable GetHospitalPurchasePlan(SuperviseQuery sq, int currentpage, int pagesize, out int totalrecord)
        {
            return HZ.Supervise.DAL.Supervise.GetHospitalPurchasePlan(sq, currentpage, pagesize, out totalrecord);

        }
        /// <summary>
        /// 获取医疗机构计划采购与实际入库的数据生产企业明细
        /// </summary>
        /// <param name="sq"></param>
        /// <param name="currentpage"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalrecord"></param>
        /// <returns></returns>
        public DataTable GetHospitalPurchasePlanByCompanySC(SuperviseQuery sq, int currentpage, int pagesize, out int totalrecord)
        {
            return HZ.Supervise.DAL.Supervise.GetHospitalPurchasePlanByCompanySC(sq, currentpage, pagesize, out totalrecord);

        }

        /// <summary>
        /// 获取医疗机构计划采购与实际入库的数据医疗机构明细
        /// </summary>
        /// <param name="sq"></param>
        /// <param name="currentpage"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalrecord"></param>
        /// <returns></returns>
        public DataTable GetHospitalPurchasePlanByHospital(SuperviseQuery sq, int currentpage, int pagesize, out int totalrecord)
        {
            return HZ.Supervise.DAL.Supervise.GetHospitalPurchasePlanByHospital(sq, currentpage, pagesize, out totalrecord);

        }


        /// <summary>
        /// 正常填报采购计划的医院列表
        /// </summary>
        /// <param name="sq"></param>
        /// <param name="currentpage"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalrecord"></param>
        /// <returns></returns>
        public DataTable GetHospitalPurchasePlanListByNormal(SuperviseQuery sq, int currentpage, int pagesize, out int totalrecord)
        {
            return HZ.Supervise.DAL.Supervise.GetHospitalPurchasePlanListByNormal(sq, currentpage, pagesize, out totalrecord);

        }


        /// <summary>
        /// 正常填报采购计划的医院列表详情
        /// </summary>
        /// <param name="sq"></param>
        /// <param name="currentpage"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalrecord"></param>
        /// <returns></returns>
        public DataTable GetHospitalPurchasePlanListByNormalDetail(SuperviseQuery sq, int currentpage, int pagesize, out int totalrecord)
        {
            return HZ.Supervise.DAL.Supervise.GetHospitalPurchasePlanListByNormalDetail(sq, currentpage, pagesize, out totalrecord);

        }


        /// <summary>
        /// 采购计划填报为0的医院列表
        /// </summary>
        /// <param name="sq"></param>
        /// <param name="currentpage"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalrecord"></param>
        /// <returns></returns>
        public DataTable GetHospitalPurchasePlanListByZero(SuperviseQuery sq, int currentpage, int pagesize, out int totalrecord)
        {
            return HZ.Supervise.DAL.Supervise.GetHospitalPurchasePlanListByZero(sq, currentpage, pagesize, out totalrecord);

        }



        /// <summary>
        /// 没有填报采购计划的医院列表
        /// </summary>
        /// <param name="sq"></param>
        /// <param name="currentpage"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalrecord"></param>
        /// <returns></returns>
        public DataTable GetHospitalPurchasePlanListByNone(SuperviseQuery sq, int currentpage, int pagesize, out int totalrecord)
        {
            return HZ.Supervise.DAL.Supervise.GetHospitalPurchasePlanListByNone(sq, currentpage, pagesize, out totalrecord);
        }
         /// <summary>
        /// 药品流水表
        /// </summary>
        /// <param name="sq"></param>
        /// <param name="currentpage"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalrecord"></param>
        /// <returns></returns>
        public DataTable GetYiBaoList(SuperviseQuery sq)
        {
            return HZ.Supervise.DAL.Supervise.GetYiBaoList(sq); 
        }


        /// <summary>
        ///  获取药品信息
        /// </summary>
        /// <param name="ProcureCatalogID"></param>
        /// <returns></returns>
        public HZ.Supervise.Model.View.ProcureCatalog GetProcureCatalogByID(string ProcureCatalogID)
        {
            return HZ.Supervise.DAL.Supervise.GetProcureCatalogByID(ProcureCatalogID); 
            
        }
        public DataTable GetMoreJQPricemingxi(string where, int currentpage, int pagesize, out int totalrecord)
        { 
            string sql = string.Format("select *,case when ISNUMERIC(JQPrice)=1 then SUBSTRING(cast(JQprice as varchar(100)),1,CHARINDEX('.',JQPrice)+3) end jqprice2 from View_JQPriceDetail where 1=1 {0} order by GoodsID", where);
            HZ.Data.PageDescribeYn pdes = new HZ.Data.PageDescribeYn(sql, pagesize) { CurrentPage = currentpage };
            DataTable dt = pdes.GetSpecailPage();
            totalrecord = pdes.TotalCount;
            return dt;
        }
        public DataTable GetMoreShenPricemingxi(string where, int currentpage, int pagesize, out int totalrecord)
        { 
            string sql = string.Format("select * from View_MaxPriceDetail where 1=1 {0} order by GoodsID", where);
            HZ.Data.PageDescribeYn pdes = new HZ.Data.PageDescribeYn(sql, pagesize) { CurrentPage = currentpage };
            DataTable dt = pdes.GetSpecailPage();
            totalrecord = pdes.TotalCount;
            return dt;
        }
    }

}
